What DBCC SHOW_STATISTICS tells me

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view.  Basically it shows you the statistics, or a summary of the data, that SQL Server will use to help generate an execution plan.

=> Create below database and table.

Create database Test
go
USE Test
GO
CREATE TABLE [dbo].[Table1](
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
) ON [PRIMARY]
GO

=> You will see a clustered index created automatically. Add the below non clustered index.

USE Test
GO
CREATE NONCLUSTERED INDEX [IX_Table1] ON [dbo].[Table1]
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

=> Insert some sample rows.

insert into Table1 values (1,2,3)
go 10
set nocount on
declare @i int
set @i =2
while (@i<20)
begin
insert into Table1 values(@i,12,13)
set @i=@i+1
end

=> Lets run dbcc show_statistics and see the output. We have inserted some rows, but we don’t see any output.

dbcc show_statistics (‘Table1′,’IX_Table1’)

blog5_1

=> Running update statistics with fullscan and then dbcc show_statistics and we get the below output. This seems to show what we have inserted.

update statistics Table1 with fullscan
dbcc show_statistics(‘Table1′,’IX_Table1’)

blog5_2

Tab1 from above screenshot

blog5_3

[$] Name : Name of the index.

[$] Updated : Date when the statistics for this index is updated or the time when it was last rebuilt.

[$] Rows : Number of rows in the underlying table.

[$] Rows Sampled : Number of rows sampled. In our case we performed an update statistics with fullscan, hence we see all the rows sampled. In most of the case, you will see less number of rows in “Rows Sampled” than the “Rows” indicating we have a sample value set.

[$] Steps : Number of steps in the histogram. In our case it is 10, since the number of rows in tab 3 as per the above screenshot is just 10.

[$] Density : Calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values. This Density value is not used by the query optimizer and is displayed for backward compatibility with versions before SQL Server 2008.

[$] Average Key Length : Average number of bytes per value for all of the key columns in the statistics object. In our case 3 columns are int.So, 4+4+4 = 12.

[$] String Index : Yes indicates the statistics object contains string summary statistics to improve the cardinality estimates for query predicates that use the LIKE operator; for example, WHERE ProductName LIKE ‘%Bike’. String summary statistics are stored separately from the histogram and are created on the first key column of the statistics object when it is of type char, varchar, nchar, nvarchar, varchar(max), nvarchar(max), text, or ntext..

[$] Unfiltered Rows : Total number of rows in the table before applying the filter expression. If Filter Expression is NULL, Unfiltered Rows is equal to Rows.

Tab2 from above screenshot.

blog5_4

[$] All Density : It is the value of 1/distinct rows.
select distinct col1 from Table1
=> Returns 19 rows => 1/19 = 0.05263158

select distinct col1,col2 from Table1
=> Returns 19 rows => 1/19 = 0.05263158

select distinct col1,col2,col3 from Table1
=> Returns 19 rows => 1/19 =0.05263158

[$] Average length : Average length, in bytes, to store a list of the column values for the column prefix.

Col1 => 4
Col1, Col2 => 4+4 = 8
Col1, Col2, col3 => 4+4+4 = 12

[$] Columns : Names of the columns in the prefix for which All density and Average length are displayed.

Tab3 from above screenshot

blog5_5

SQL Server builds the histogram from the sorted set of column values in three steps as per https://technet.microsoft.com/en-us/library/dd535534(v=SQL.100).aspx.

=> Histogram initialization: In the first step, a sequence of values starting at the beginning of the sorted set is processed, and up to 200 values of RANGE_HI_KEY,EQ_ROWS, RANGE_ROWS, and DISTINCT_RANGE_ROWS are collected (RANGE_ROWS and DISTINCT_RANGE_ROWS are always zero during this step). The first step ends either when all input has been exhausted, or when 200 values have been found.

=> Scan with bucket merge: Each additional value from the leading column of the statistics key is processed in the second step, in sorted order; for each successive value, either the value is added to the last range, or a new range at the end is created (this is possible because the input values are sorted). If a new range is created, one pair of existing, neighboring ranges is collapsed into a single range. This pair of ranges is selected in order to minimize information loss. The number of histogram steps after the ranges are collapsed stays at 200 throughout this step. This method is based on a variation of the maxdiff histogram.

=> Histogram consolidation: In the third step, more ranges can be collapsed if a significant amount of information is not lost. Therefore, even if the column has more than 200 unique values, the histogram might have less than 200 steps.

[$] RANGE_HI_KEY : This is the key value. This value can be anything from the below output.
select distinct col1 from Table1

[$] RANGE_ROWS : Number of rows whose column value falls between the values as marked in below screenshot. If we take the second row, We have 1 column between RANGE_HI_KEY “1” and “3”, which is value “2”.

blog5_6

To understand this further, insert close to 100000 rows,

set nocount on
declare @i int
set @i =29
while (@i<100000)
begin
insert into Table1 values(@i,12,13)
set @i=@i+1
end

Now check the output of TAB3 again,

blog5_7

It is pretty evident, that between RANGE_HI_KEY “1” and “99998” we have 99987 rows.

[$] EQ_ROWS : Total number of rows equal to corresponding RANGE_HI_KEY.
From the above screenshot,

Row 1 => RANGE_HI_KEY is 1 => select COUNT(*) from table1 where col1=1 => 10, which is equal to EQ_ROWS.

Row 2 => RANGE_HI_KEY is 99998 => select COUNT(*) from table1 where col1=99998 => 1, which is equal to EQ_ROWS.

Row 3 => RANGE_HI_KEY is 99999 => select COUNT(*) from table1 where col1=99999 => 1, which is equal to EQ_ROWS.

[$] DISTINCT_RANGE_ROWS : Number of distinct rows between each of the values in RANGE_HI_KEY. From the above screenshot,

Row 1 => RANGE_HI_KEY is 1 => This is the first row and it will always be 0.

Row 2 => RANGE_HI_KEY is 99998 => select distinct count(col1) from table1 where col1>1 and col1<99998 => 99987, which is equal to  DISTINCT_RANGE_ROWS.

Row 3 => RANGE_HI_KEY is 99999 => select distinct count(col1) from table1 where col1>99998 and col1<99999 => 0, which is equal to DISTINCT_RANGE_ROWS.

[$] AVG_RANGE_ROWS : Average number of rows with duplicate column values within a histogram step, excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0).

Row 1 => RANGE_HI_KEY is 1 => DISTINCT_RANGE_ROWS = 0, Hence AVG_RANGE_ROWS is 1.

Row 2 => RANGE_HI_KEY is 99998 => DISTINCT_RANGE_ROWS > 0, (RANGE_ROWS / DISTINCT_RANGE_ROWS) =>(99987 / 99987) => 1, which is same as AVG_RANGE_ROWS.

Row 3 => RANGE_HI_KEY is 99999 => DISTINCT_RANGE_ROWS = 0, Hence AVG_RANGE_ROWS is 1.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Hide Instance set to YES in SQL server Network Configuration

-> I was working on a 2 node cluster server where SQL server was failing to come online.

-> I tried starting the SQL services as a standalone service, that is tried starting the SQL services from services.msc or SQL server configuration manager and it started fine.

-> Started the SQL services from cluster administrator and it started to loop within the group on the physical node 1 and it failed.

-> I failed over the SQL server to physical node 2. All the resources came online.

-> I failed back to physical node 1 and the SQL server resources failed again.

-> Checked the SQL server errorlog and did not find any errors in it. I generated the cluster.log and found the below entry,

00001eb4.00003870::2016/11/31-01:43:04.009 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] SvcTerminate: Service is stopped.
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
00001eb4.00002d14::2016/11/31-01:43:35.116 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ConnectToSQL: asked to terminate while trying to connect to server.
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] OnlineThread: Error connecting to SQL Server.
00001eb4.00002d14::2016/11/31-01:43:35.116 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] OnlineThread: asked to terminate while waiting for QP.
00001eb4.00002d14::2016/11/31-01:43:35.116 WARN  [RHS] returning ResourceExitStateTerminate.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] HandleMonitorReply: TERMINATERESOURCE for ‘SQL Server (Instance_Name)’, gen(8) result 0.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] TransitionToState(SQL Server (Instance_Name)) [Terminating to Failed]–>Failed.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] rcm::RcmGroup::UpdateStateIfChanged: (<NODE1>, Pending –> Failed)

-> I tried starting the SQL service as a standalone service and tried connecting the SQL server using management studio. Could not connect to the SQL server instance.

-> It seems like SQL services starts fine, but cluster services cannot connect to the instance.

-> Opened SQL server configuration manager and checked for any bad aliases. Could not find any aliases created.

-> Opened the SQL server configuration manager. Opened the “Protocols for <Instance_Name>” under “SQL server network configuration”. “Hide Instance” was set to Yes. Please refer https://msdn.microsoft.com/en-us/library/ms179327(v=sql.110).aspx for more info on hide instances.

blog4_1

-> I changed “Hide Instance” to NO. Started the SQL services on cluster administrator and it started fine.

-> I tried failing over and failing back the SQL services between the 2 nodes and everything was online.

Thank You,
Karthik Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Database ‘model’ cannot be opened. It is in the middle of a restore.

-> SQL server was not coming online in one of the database server.

-> I checked the SQL server log and found the below error,

.
.
2016-12-25 19:43:43.02 spid12s     Starting up database ‘msdb’.
2016-12-25 19:43:43.03 spid10s     Starting up database ‘model’.
2016-12-25 19:43:43.05 spid10s     The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.
2016-12-25 19:43:43.05 spid10s     Error: 927, Severity: 14, State: 2.
2016-12-25 19:43:43.05 spid10s     Database ‘model’ cannot be opened. It is in the middle of a restore.
2016-12-25 19:43:43.05 spid10s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2016-12-25 19:43:43.05 spid10s     SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
.
.

-> As per the SQL server errorlog, it seems like the model database is restoring. But in reality we see this error when model database is corrupted. The error related to tempdb is because tempdb is recreated everytime SQL server starts using model database.

-> Open services.msc and get to the SQL server services. Right click the service and click on properties. In General tab, look for “path to executable”. Copy the SQLServr.exe location from there.

-> Open a command prompt and navigate to the location. Now type the below command,

sqlservr.exe -s<InstanceName> -T3608 -T3609 -c -f
3608 Recovers master database only. Skips automatic recovery (at startup) for all databases except the master database.
3609 Skips the creation of the tempdb database at startup. Use this trace flag if the tempdb database is problematic or problems exist in the model database.
 -c Start as a console and not service.
 -f Minimal configuration mode.

-> Open another command prompt and use the below command,

SQLCMD -E -SADMIN:<Hostname\InstanceName>

-> Execute the below command,

use master
go
sp_detach_db ‘model
go

-> Execute the below command to attach the database,

CREATE DATABASE [model] ON (FILENAME = N'<Location>\model.mdf’ ),( FILENAME = N'<Location>\modellog.ldf’ )
FOR ATTACH

-> If you get an error that the files cannot be attached. You will have to copy the MDF and LDF file from a different instance of same build and issue the above command again.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.