CB-Net

SQL : AUTO STATS Troubleshooting

Statistics for Query Optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.

To view the indexes and their last update time use the following command:

 SELECT
        o.name AS Table_Name
       ,i.name AS Index_Name
       ,STATS_DATE(o.id,i.indid) AS Date_Updated

FROM
        sysobjects o JOIN
        sysindexes i ON i.id = o.id

WHERE
        xtype = ‘U’ AND
        i.name IS NOT NULL

ORDER BY
        o.name ASC
       ,i.name ASC

To update statistic:

•     On a single index: UPDATE STATISTICS [Table_Name][INDEX_NAME]
•     On a single table: UPDATE STATISTICS[TABLE_NAME]
•     On the entire Database: EXEC sp_updatestats

SQL : Common SQL Wait Types

Using the command DBCC SQLPERF(WAITSTATS) you can get real time totals of all wait types within an SQL instance. The table below outlines some common wait types and what to look for when troubleshooting them. 

SQL Troubleshooting Wait Time

The following SQL script will export the output of ‘DBCC SQLPERF(WAITSTATS)’ into a temporary table named #waitstats.

CREATE TABLE #waitstats
(
    [Wait Type]        nvarchar(32) not null,
    [Requests]         float not null,
    [Wait Time]        float not null,
    [Signal Wait Time] float not null
)

INSERT INTO #waitstats EXEC(‘dbcc sqlperf(waitstats)’)

SELECT * FROM #waitstats
ORDER BY ‘Wait Time’ DESC

To delete the temporary table use the command:

DROP TABLE #waitstats 

The following SQL script will identify fragmented indexes and automaticallyrebuild them. Note that this should not be run duringbusy periods/production hours and should only be run against databases which are not partitioned:

{code lang:sql showtitle:false lines:false hidden:false}declare @tablename varchar (128)
declare @id int
declare @Cnt smallint
declare @nrc_mask int
declare tables cursor for
select TABLE_SCHEMA + ‘.’ + TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘BASE TABLE’

create table #spt_space
( aidi int,
[rows] int null,
reserved dec(15) null,
AUTOSTATS char(2) null,
[Last Updated] datetime
)
open tables
/*
** Set NORECOMPUTE mask
*/
set @nrc_mask = 16777216

fetch next
from tables
into @tablename
while @@fetch_status = 0
begin

select @id = object_id(@tablename)

insert into #spt_space (aidi, reserved)
select @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
update #spt_space set [rows] = i.[rows]
from #spt_space inner join sysindexes i
on #spt_space.aidi = i.id
where i.indid =indid and i.id = @id

update #spt_space set [AUTOSTATS] =
case (si.status & @nrc_mask)
when @nrc_mask then ‘OFF’
else ‘ON’
end,
[Last Updated] = stats_date(#spt_space.aidi, si.indid)
from sysindexes si inner join #spt_space
on #spt_space.aidi = si.id
where si.id = @id AND — Table
si.indid BETWEEN 1 AND 254 — Skip HEAP/TEXT index
fetch next
from tables
into @tablename
end

— Close and deallocate the cursor
close tables
deallocate tables

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

CREATE TABLE #bigtables (i int NOT NULL IDENTITY(1,1), tablename varchar(128))
INSERT #bigtables(tablename)
SELECT object_name(aidi) as tablename
FROM #spt_space
–WHERE rows >= 100000

SELECT * FROM #spt_space
DROP TABLE #spt_space
SELECT @Cnt = count(*) FROM #bigtables

DECLARE @DTime datetime
Set @DTime = GETDATE()

SET @id = 1
WHILE @id <= @Cnt
BEGIN
SELECT @tablename = tablename FROM #bigtables WHERE i = @id
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST’)
SET @id = @id + 1
END

SELECT ObjectName, IndexName, CountPages, ExtentSwitches, BestCount, ActualCount, LogicalFrag FROM #fraglist ORDER BY LogicalFrag DESC
DROP Table #bigtables
DROP table #fraglist{/code}

Use the following code to rebuild indexes that are 10% or more fragmented (you can change this threshold by modifying the figure highlighted in RED):

{code lang:sql title:”SQL Query – Fix Fragmentation” lines:false hidden:false}declare @tablename varchar (128) 

declare @id int 
declare @Cnt smallint 
declare @nrc_mask int 
declare tables cursor for 
select TABLE_SCHEMA + ‘.’ + TABLE_NAME 
from INFORMATION_SCHEMA.TABLES 
where TABLE_TYPE = ‘BASE TABLE’ 

create table #spt_space 
( aidi int, 
[rows] int null, 
reserved dec(15) null, 
AUTOSTATS char(2) null, 
[Last Updated] datetime 
) 
open tables 
/* 
** Set NORECOMPUTE mask 
*/ 
set @nrc_mask = 16777216 

fetch next 
from tables 
into @tablename 
while @@fetch_status = 0 
begin 

select @id = object_id(@tablename) 

insert into #spt_space (aidi, reserved) 
select @id, sum(reserved) 
from sysindexes 
where indid in (0, 1, 255) 
and id = @id 
update #spt_space set [rows] = i.[rows] 
from #spt_space inner join sysindexes i 
on #spt_space.aidi = i.id 
where i.indid =indid and i.id = @id 

update #spt_space set [AUTOSTATS] = 
case (si.status & @nrc_mask) 
when @nrc_mask then ‘OFF’ 
else ‘ON’ 
end, 
[Last Updated] = stats_date(#spt_space.aidi, si.indid) 
from sysindexes si inner join #spt_space 
on #spt_space.aidi = si.id 
where si.id = @id AND — Table 
si.indid BETWEEN 1 AND 254 — Skip HEAP/TEXT index 
fetch next 
from tables 
into @tablename 
end 

— Close and deallocate the cursor 
close tables 
deallocate tables 

CREATE TABLE #fraglist ( 
ObjectName CHAR (255), 
ObjectId INT, 
IndexName CHAR (255), 
IndexId INT, 
Lvl INT, 
CountPages INT, 
CountRows INT, 
MinRecSize INT, 
MaxRecSize INT, 
AvgRecSize INT, 
ForRecCount INT, 
Extents INT, 
ExtentSwitches INT, 
AvgFreeBytes INT, 
AvgPageDensity INT, 
ScanDensity DECIMAL, 
BestCount INT, 
ActualCount INT, 
LogicalFrag DECIMAL, 
ExtentFrag DECIMAL) 

CREATE TABLE #bigtables (i int NOT NULL IDENTITY(1,1), tablename varchar(128)) 
INSERT #bigtables(tablename) 
SELECT object_name(aidi) as tablename 
FROM #spt_space 
–WHERE rows >= 100000 

SELECT * FROM #spt_space 
DROP TABLE #spt_space 
SELECT @Cnt = count(*) FROM #bigtables 

DECLARE @DTime datetime 
Set @DTime = GETDATE() 

SET @id = 1 
WHILE @id <= @Cnt 
BEGIN 
SELECT @tablename = tablename FROM #bigtables WHERE i = @id 
INSERT INTO #fraglist 
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) 
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST’) 
SET @id = @id + 1 
END 

SELECT ObjectName, IndexName, CountPages, ExtentSwitches, BestCount, ActualCount, LogicalFrag FROM #fraglist ORDER BY LogicalFrag DESC 

DROP Table #bigtables 

/* ********************************* */
/* Perform the REINDEX – NOTE This will lock the Table in which the index resides */
/* ********************************* */

DECLARE @objectid int, @indexname varchar(200), @frag decimal, @SQL varchar(500)
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= 10
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0;
— Open the cursor.
OPEN indexes;
— Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT ‘Executing DBCC DBREINDEX (‘ +RTRIM(@tablename) + ‘,”’ + RTRIM(@indexname)
+ ”’) – LogicalFrag Currently: ‘ + RTRIM(CONVERT(VARCHAR(15),@frag)) + ‘%’;

SELECT @SQL = ‘DBCC DBREINDEX (‘ +RTRIM(@tablename) + ‘,”’ + RTRIM(@indexname) + ”’)’
EXEC (@SQL)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
END;
— Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
— Delete the temporary table.
DROP TABLE #fraglist;
GO

EXEC sp_updatestats

{/code}