UNDERSTANDING SQL SERVER STATISTICS

If there’s an upcoming election and you are running for office and getting ready to go from town to town city to city with your flyers, you will want to know approximately how many flyers you’re going to bring.

If you’re the coach of a sports team, you will want to know your players’ stats before you decide who to play when, and against who. You will often play a matchup game, even if you have 20 players, you might be allowed to play just 5 at a time, and you will want to know which of your players will best match up to the other team’s roster. And you don’t want to interview them one by one at game time (table scan), you want to know, based on their statistics, who your best bets are.

Just like the election candidate or the sports coach, SQL Server tries to use statistics to “react intelligently” in its query optimization. Knowing number of records, density of pages, histogram, or available indexes help the SQL Server optimizer “guess” more accurately how it can best retrieve data. A common misnomer is that if you have indexes, SQL Server will use those indexes to retrieve records in your query. Not necessarily. If you create, let’s say, an index to a column City and <90% of the values are ‘Vancouver’, SQL Server will most likely opt for a table scan instead of using the index if it knows these stats.

For the most part, there *may* be minimal we need to do to keep our statistics up-to-date (depending on your configurations), but understanding statistics a little bit better is in order to help us understand SQL Server optimization a little bit more.

How are statistics created?

Statistics can be created different ways
- Statistics are automatically created for each index key you create.

- If the database setting autocreate stats is on, then SQL Server will automatically create statistics for non-indexed columns that are used in queries.

- CREATE STATISTICS

What do statistics look like?

If you’re curious, there’s a couple ways you can peek at what statistics look like.

Option 1 – you can go to your Statistics node in your SSMS, right click > Properties, then go to Details. Below is a sample of the stats and histogram that’s collected for one of the tables in my database

Option 2 – you can use DBCC SHOW_STATISTICS WITH HISTOGRAM

The histograms are a great way to visualize the data distribution in your table.

How are statistics updated?

The default settings in SQL Server are to autocreate and autoupdate statistics.

Notice that there are two (2) options with the Auto Update statistics.
- Auto Update Statistics basically means, if there is an incoming query but statistics are stale, SQL Server will update statistics first before it generates an execution plan.
- Auto Update Statistics Asynchronously on the other hand means, if there is an incoming query but statistics are stale, SQL Server uses the stale statistics to generate the execution plan, then updates the statistics afterwards.

However, if you want to manually update statistics, you can use either sp_updatestats or UPDATE STATISTICS <statistics name>

How do we know statistics are being used?

One good check you can do is when you generate execution plans for your queries:

check out your “Actual Number of Rows” and “Estimated Number of Rows”.

If these numbers are (consistently) fairly close, then most likely your statistics are up-to-date and used by the optimizer for the query. If not, time for you to re-check your statistics create/update frequency.

What configuration settings should we set?

There may be cases when you may want to disable statistics update temporarily while you’re doing massive updates on a table, and you don’t want it to be slowed down by the autoupdate.

However, for the most part, you will want to keep the SQL Server settings:
- auto create statistics
- auto update statistics

References:

Rob Carrol. http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx

Elisabeth Redei has an excellent 3-part series on SQL Server Statistics:
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-ii.aspx
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/12/17/lies-damned-lies-and-statistics-part-iii-sql-server-2008.aspx

How to create database dynamically in sqlserver

 
DECLARE @Query VARCHAR(MAX)=''
DECLARE @DbName VARCHAR(400)  
set @DbName= @DatabaseName
SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON  PRIMARY '
SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
SET @Query = @Query + ' LOG ON '
SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
exec(@query)
set @Query='USE ['+@DbName+']'+' '+'CREATE TABLE [dbo].[tablename]([ID] [int] IDENTITY(1,1) NOT NULL,[col1] [nvarchar](250) NOT NULL,[clo2] [nvarchar](250) NOT NULL,[col3] [nvarchar](250) NOT NULL,[col4] [int] NOT NULL,[col5] [datetime] NOT NULL,[col6] [int] NULL,[col7] [datetime] NULL,[IsDeleted] [bit] NOT NULL,CONSTRAINT [PK_col1] PRIMARY KEY CLUSTERED([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]'  
print @query
exec(@query)
 

how to create report in rdlc

MAY

22

Using RDLC and DataSets to develop ASP.NET Reporting Services Reports

ASP.net - RDLC Report in ASP.net

In this article i will show you how to generate RDLC report in ASP.net web application.

Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server

Step 3
Create a web application and give solution name as SolRDLCReportASP.

Step 4

Add AJAX ScriptManger on page,it is look like this

view plainprint?

1. <asp:ScriptManager ID="ScriptManager1" runat="server">  
2. </asp:ScriptManager>  

Step 5
Add a ReportViwer control on page from toolbox,it is look like this

Click on image for better view

view plainprint?

1. <asp:UpdatePanel ID="UpdatePanel1" runat="server">  
2.             <ContentTemplate>  
3.                   
4.                 <rsweb:ReportViewer ID="EmployeeReport" runat="server" Width="100%" Height="100%">  
5.               
6.                 </rsweb:ReportViewer>  
7.   
8.             </ContentTemplate>  
9.         </asp:UpdatePanel>  

Finally Presentation part done now we Create DataSet Schema and Report Design.


Step 6

First We create a DataSet Schema.it can be define Dataset schema without connecting to any datasource.

Add a DataSet Schema,right click on Add new Item,select DataSet from installed Visual Studio templates and name it NorthwindDataSet and click on add button,it is look like this

Click on image for better view

Step 7

Click on toolbox icon,it is look like this

Click on image for better view

Select DataTable from Toolbox and drag and drop to the dataset design editor,it is look like this

Click on image for better view

Finally Add column to schema,it is look like this

Click on image for better view

DataSet Schema is ready now we create Report Design in web application.

Step 8

Add a RDLC Report,First Create App_Data folder,right click on App_Data folder,select  Add new Item,select Report from installed Visual Studio templates and name it NorthwindReport and click on add button,it is look like this

Click on image for better view

Step 9

Add DataSet Schema to the report,it is look like this

Click on image for better view

In the next dialog, give the dataset a name called EmployeeDataSet. Change the data source to NorthwindDataSet and select available dataset Employee and click OK,it is look like this

Click on image for better view

Step 10

Add Header and Footer on report,it is look like this

Click on image for better view

In Header Section Add TextBox from toolbox,it is look like this

Click on image for better view

In Footer Section Add Page number from build in field,it is look like this

Click on image for better view

Step 11

Add Table from toolbox for display employee data,it is look like this

Click on image for better view

Drag and Drop all Employee Fields from NorthwindDataSet into table,it is look like this

Click on image for better view

Finally Report is ready now we move to programming part.

Step 12

Bind Employee data to Dataset Schema,it is look like this

view plainprint?

1. #region Bind Employee Data to DataSet Schema  
2.    /// <summary>  
3.    /// Get Employee data from Northwind database and bind in NorthwindDataSet  
4.    /// </summary>  
5.    /// <returns>DataTable</returns>  
6.    private DataTable GetEmployeeData()  
7.    {  
8.        try  
9.        {  
10.            // Open Sql Connection  
11.            SqlConnection SqlCon = new SqlConnection(@"Data Source=SHREE\SHREE;Initial Catalog=Northwind;Integrated Security=True");  
12.            SqlCon.Open();  
13.   
14.            // Create a Command  
15.            SqlCommand SqlComm = new SqlCommand();  
16.            SqlComm.Connection = SqlCon;  
17.            SqlComm.CommandType = CommandType.Text;  
18.            SqlComm.CommandText = "SELECT FirstName,LastName,BirthDate,Address,City,PostalCode,Country FROM Employees";  
19.   
20.            // Create instance of Northwind DataSetXSD  
21.            NorthwindDataSet.EmployeeDataTable EmployeeDt = new NorthwindDataSet.EmployeeDataTable();  
22.   
23.            // Set a Data Commands  
24.            SqlDataAdapter SqlDa = new SqlDataAdapter(SqlComm);  
25.            SqlDa.Fill(EmployeeDt); // Fill Data in NorthwindDataSet Object.  
26.   
27.            return EmployeeDt;  
28.   
29.        }  
30.        catch (Exception ex)  
31.        {  
32.            throw new Exception(ex.Message);  
33.        }  
34.    }  
35.  
36.    #endregion  

Step 13
Display Report in Report Viewer,it is look like this

view plainprint?

1. #region Display Report  
2.     /// <summary>  
3.     /// Display Report in Report Viewer  
4.     /// </summary>  
5.     private void DisplayReport()  
6.     {  
7.         try  
8.         {  
9.            // Clear the Data Source   
10.            EmployeeReport.LocalReport.DataSources.Clear();  
11.   
12.            // Set a DataSource to the report  
13.   
14.            // First Parameter - Report DataSet Name  
15.            // Second Parameter - DataSource Object i.e DataTable  
16.            EmployeeReport.LocalReport.DataSources.Add(new ReportDataSource("EmployeeDataSet",GetEmployeeData()));  
17.   
18.            // OR Set Report Path  
19.            EmployeeReport.LocalReport.ReportPath = HttpContext.Current.Server.MapPath("~/App_Data/NorthwindReport.rdlc");  
20.   
21.            // Refresh and Display Report  
22.            EmployeeReport.LocalReport.Refresh();  
23.         }  
24.         catch (Exception ex)  
25.         {  
26.             throw new Exception(ex.Message);  
27.         }  
28.     }  
29.  
30.     #endregion  

Call DisplayReport function on Page_Load event,it is look like this

view plainprint?

1. protected void Page_Load(object sender, EventArgs e)  
2.    {  
3.        try  
4.        {  
5.            if (IsPostBack == false)  
6.            {  
7.                DisplayReport();  
8.            }  
9.        }  
10.        catch (Exception ex)  
11.        {  
12.            throw new Exception(ex.Message);    
13.        }  
14.    }  

Run the project.


Output

Click on image for better view


Full Code


1. .Aspx Page Code

view plainprint?

1. <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>  
2.   
3. <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"  
4.     Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>  
5.   
6. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
7.   
8. <html xmlns="http://www.w3.org/1999/xhtml">  
9. <head runat="server">  
10.     <title></title>  
11. </head>  
12. <body>  
13.     <form id="form1" runat="server">  
14.     <div>  
15.           
16.         <asp:ScriptManager ID="ScriptManager1" runat="server">  
17.         </asp:ScriptManager>  
18.           
19.         <asp:UpdatePanel ID="UpdatePanel1" runat="server">  
20.             <ContentTemplate>  
21.                   
22.                 <rsweb:ReportViewer ID="EmployeeReport" runat="server" Width="100%" Height="100%">  
23.               
24.                 </rsweb:ReportViewer>  
25.   
26.             </ContentTemplate>  
27.         </asp:UpdatePanel>  
28.           
29.   
30.           
31.     </div>  
32.     </form>  
33. </body>  
34. </html>  

2. Code behind

view plainprint?

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Web;  
5. using System.Web.UI;  
6. using System.Web.UI.WebControls;  
7. using System.Data;  
8. using System.Data.SqlClient;  
9. using Microsoft.Reporting.WebForms;  
10.   
11. public partial class _Default : System.Web.UI.Page   
12. {  
13.     protected void Page_Load(object sender, EventArgs e)  
14.     {  
15.         try  
16.         {  
17.             if (IsPostBack == false)  
18.             {  
19.                 DisplayReport();  
20.             }  
21.         }  
22.         catch (Exception ex)  
23.         {  
24.             throw new Exception(ex.Message);    
25.         }  
26.     }  
27.  
28.     #region Bind Employee Data to DataSet Schema  
29.     /// <summary>  
30.     /// Get Employee data from Northwind database and bind in NorthwindDataSet  
31.     /// </summary>  
32.     /// <returns>DataTable</returns>  
33.     private DataTable GetEmployeeData()  
34.     {  
35.         try  
36.         {  
37.             // Open Sql Connection  
38.             SqlConnection SqlCon = new SqlConnection(@"Data Source=SHREE\SHREE;Initial Catalog=Northwind;Integrated Security=True");  
39.             SqlCon.Open();  
40.   
41.             // Create a Command  
42.             SqlCommand SqlComm = new SqlCommand();  
43.             SqlComm.Connection = SqlCon;  
44.             SqlComm.CommandType = CommandType.Text;  
45.             SqlComm.CommandText = "SELECT FirstName,LastName,BirthDate,Address,City,PostalCode,Country FROM Employees";  
46.   
47.             // Create instance of Northwind DataSetXSD  
48.             NorthwindDataSet.EmployeeDataTable EmployeeDt = new NorthwindDataSet.EmployeeDataTable();  
49.   
50.             // Set a Data Commands  
51.             SqlDataAdapter SqlDa = new SqlDataAdapter(SqlComm);  
52.             SqlDa.Fill(EmployeeDt); // Fill Data in NorthwindDataSet Object.  
53.   
54.             return EmployeeDt;  
55.   
56.         }  
57.         catch (Exception ex)  
58.         {  
59.             throw new Exception(ex.Message);  
60.         }  
61.     }  
62.  
63.     #endregion  
64.   
65.     #region Display Report  
66.     /// <summary>  
67.     /// Display Report in Report Viewer  
68.     /// </summary>  
69.     private void DisplayReport()  
70.     {  
71.         try  
72.         {  
73.            // Clear the Data Source   
74.            EmployeeReport.LocalReport.DataSources.Clear();  
75.   
76.            // Set a DataSource to the report  
77.   
78.            // First Parameter - Report DataSet Name  
79.            // Second Parameter - DataSource Object i.e DataTable  
80.            EmployeeReport.LocalReport.DataSources.Add(new ReportDataSource("EmployeeDataSet",GetEmployeeData()));  
81.   
82.            // OR Set Report Path  
83.            EmployeeReport.LocalReport.ReportPath = HttpContext.Current.Server.MapPath("~/App_Data/NorthwindReport.rdlc");  
84.   
85.            // Refresh and Display Report  
86.            EmployeeReport.LocalReport.Refresh();  
87.         }  
88.         catch (Exception ex)  
89.         {  
90.             throw new Exception(ex.Message);  
91.         }  
92.     }  
93.  
94.     #endregion  
95. }