Troubleshooting Microsoft SQL Server

Do we need to run UPDATE STATISTICS WITH FULLSCAN

Auto-Update Statistics

For a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide. Auto create and auto update statistics are on by default. If you observe bad plans and suspect that missing or out of date statistics are at fault, verify that auto create and auto update statistics are on. If enable, then check when last updated. If recently updated, then run-running with FULL SCAN will probably take hours to run and NOT improve query performance.

Question: Do we really need to troubleshooting Query performance by running UPDATE STATS with FULL SCAN when AUTO UPDATE STATS is enable?

Answer: Probably Not!

1) Use sp_helpdb <database> to see if AUTO STATS are enabled.

sp_helpdb <db name>

>>>>>IsAutoUpdateStatistics

2) If AUTO is enable, verify when STATS were last updated.

DBCC show_statistics ('Shippers', 'PK_Shippers') WITH STAT_HEADER

Name Updated

------------------------------ --------------------

PK_Shippers Jul 16 2008 11:47AM

select stats_date(object_id('Shippers'),1)

-----------------------

2008-07-16 11:47:34.697

“Statistics are a histogram consisting of an even sampling of values for the index key (or the first column of the key for a composite index) based on the current data. The histogram is stored in the statblob field of the sysindexes table, which is of type image." -- Kalen Delaney

If STATS were recently updated, then FULL SCAN may take hours to perform on a large table and may not return any more accurate statistics then already collected. Also, the next time AUTO runs it will overwrite the all the statistics generated from the FULL SCAN. Rememer, both FULL SCAN and AUTO STATS are updating the same data in sysindexes, so whomever runs last are stats used by the Query Optimizer.

Rule of thumb!

--------------------

Use Update Stats with full scan and disable Auto Stats when doing large batch updates and minimal to none ongoing updates.

Use Auto Stats (default) and don't run UPDATE STATS Full Scan when data is modified throughout the day.

Additional Information

-------------------------------

In SQL 2000 Auto Update stats monitors the rowmodctr value of sysindexes which records number of data changes. If the number of data changes exceeds a threshold the statistics are updated. When Statistics are updated this value is reset to 0. When statistics are update, Query Optimizer will load a new query plan. When the Optimizer threshold is not reach but data modifications have occurred, Update Statistics (FULL or Partial) will force new statistics and new query plan.

In SQL 2005/2008 changes are now tracked by column. The rowmodctr provides overall indication of data changes to a table/index but it alone doesn't trigger update stats.

An alternative to enabling auto create statistics is enabled or make sure to manually create statistics using CREATE STATISTICS or sp_createstats. Note that auto-statistics will not work for read-only databases.

This info from ianjo's blog (http://blogs.msdn.com/ianjo/archive/2005/11/10/491549.aspx) and from the “Inside SQL Server 2000" book by Kalen Delaney.

--Chris Skorlinski

--Microsoft SQL Server Escalation Services

Posted Wednesday, August 27, 2008 10:29 AM by chrissk | 1 Comments

Filed under: STATISTICS FULLSCAN

Troubleshooting Transactional Replication Distribution Agent Latency

Here is a tip I found while troubleshooting a Distribution Agent latency problem. We were trying to see why on some days the Distribution Agent was "getting behind". We suspected it was volume related. Below are a few queries to help uncover workload in the Distribution database.

The query below returns count of transactions. It's not fancy, but it helps identify days with higher then average counts.

--Return count of transactions by day by publication

select datepart(dayofyear,entry_time) as 'day of year',

publisher_database_id as 'pub db id',

count(*) as 'count of trans' from MSrepl_transactions

where entry_time < getdate() - 5 --count trans older then 5 days

group by datepart(dayofyear,entry_time), publisher_database_id

order by datepart(dayofyear,entry_time), publisher_database_id

day of year pub db id count of trans

----------- ----------- --------------

302 7 2291

303 7 319369

304 7 420476

305 7 4633313

306 7 411236

307 7 459866

308 7 755705

309 7 937608

310 1 57326

310 2 18950

310 7 462553

310 8 122002

311 1 38203

311 2 24254

311 7 257801

311 8 105436

--Chris Skorlinski

Posted Tuesday, June 24, 2008 10:56 AM by chrissk | 1 Comments

Query to return list of Dynamic Management Views

I don't always remember names of the SQL Server Dynamic Managment views. Books Online is great, but here is a simple query to get a list of DMVs

SELECT * FROM sys.all_objects

WHERE [name] LIKE '%dm_%'

AND [type] IN ('V', 'TF', 'IF')

AND [schema_id] = 4

ORDER BY [name]

Posted Tuesday, June 24, 2008 10:25 AM by chrissk | 1 Comments

Filed under: SQL DMV

Performance Dashboard error "Difference of two datetime values caused an overflow at runtime"

Have you tried using SQL Server Performance Dashboard? We're using this tool on SQL Server Escalation team with customers to troubleshoot real-time performance problems. It is a great way to quickly find queries with high-CPU, high-reads, waits, etc.

SQL Server 2005 Performance Dashboard Reports

http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en

We discovered a problem with one of the stored procedure when it calculates idle_connection_time for connections > 24 days. The revised stored procedure usp_Main_GetSessionInfo corrects this problem.

USE [msdb]

GO

/****** Object: StoredProcedure [MS_PerfDashboard].[usp_Main_GetSessionInfo] Script Date: 06/19/2008 15:35:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]

as

begin

select count(*) as num_sessions,

sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,

sum(convert(bigint, s.cpu_time)) as cpu_time,

sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time,

--sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

--FIX to correct for sessions connected >24 days

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))

else NULL

end as cache_hit_ratio

from sys.dm_exec_sessions s

where s.is_user_process = 0x1

end

Posted Thursday, June 19, 2008 3:51 PM by chrissk | 2 Comments

Filed under: SQL Server Performance Dashboard

I/O requests taking longer than 15 seconds to complete on file

What does the “I/O request” error below represent?

2008-04-21 13:26:42.480 spid364 Microsoft SQL Server 2005 - 9.00.3177.00 (Intel X86)

2008-04-22 16:30:02.140 spid6s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\sql data files\xxx.MDF] in database [xxx] (5).

2008-04-22 16:32:08.780 spid6s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\sql data files\xxx_data_4.NDF] in database [xxx] (5).

Research and Findings

---------------------------------

These errors may occur as result of “CPU Drift” and can be ignored and disabled, howerver, first verify both SQL DMV sys.dm_io_pending_io_requests and Windows Performance counters don’t indicate any IO delays.

On computers with multiple CPUs, the CPUs are designed to “sleep” during periods of low workload. When CPUs sleep, SQL may not accurately determine CPUs overall workload and incorrectly report this as IO WARNING shown above, however, this does not represent an actual CPU performance problem.

To confirm if the CPUs entered low-power state, SQL Server Escalation Services created RDTSC (Read Time Stamp Counter) utility to report total CPU sleep time. The report confirmed the CPUs were sleeping up to 24 seconds. This would be enough for SQL Server to incorrectly report this a slow IO. Both AMD’s and INTEL’s web sites describe the ability for CPUs to sleep.

RDTSCTest.exe [-md|-mt]

-md Detailed output (default)

-mt CPU speeds in table format

-- Current CPU Speeds --

Runtime CPU ExpectedMHz ActualMHz RDTSCTicks DriftMS

-------------------- ---- ----------- --------- -------------------- ----------------

2008-04-22 17:53:36 0 3502 3503 0x0001564772F87FA72 16123.4

2008-04-22 17:53:36 8 3502 3506 0x00015647D8B7AE21D 23922.5

2008-04-22 17:53:36 16 3502 3507 0x00015647B5FEB4A39 21260.9

For more information on RDTSC can be found at

http://blogs.msdn.com/psssql/archive/2007/08/19/sql-server-2005-rdtsc-truths-and-myths-discussed.aspx

Starting with SQL 2005 SP2 we’ve included two trace flags to disable the reporting of CPU Drift errors in the SQL Server errorlog.

Disable this error using Trace Flag 8033

The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.

Disable this error using Trace Flag 830

SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete

Posted Thursday, June 19, 2008 12:21 PM by chrissk | 0 Comments

Script to purge TokenAndPermUserStore

Here is a script that purge the 'TokenAndPermUserStore' every time it reaches 100MB.

DECLARE @CacheSize int

DECLARE @CurDate varchar(30)

WHILE (1=1)

BEGIN

SET @CurDate = CONVERT (varchar, GETDATE(), 126)

SELECT @CacheSize = SUM(single_pages_kb + multi_pages_kb)

FROM sys.dm_os_memory_clerks

WHERE name = 'TokenAndPermUserStore'

-- Greater than 100MB

IF @CacheSize >= 100000

BEGIN

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

RAISERROR ('Cleared TokenAndPermUserStore at %s, size = %dKB', 0, 1, @CurDate,

@CacheSize) WITH LOG

END

RAISERROR ('TokenAndPermUserStore size at %s: %dKB', 0, 1, @CurDate, @CacheSize)

WITH NOWAIT

WAITFOR DELAY '00:00:10'

END

Posted Thursday, June 19, 2008 12:17 PM by chrissk | 0 Comments

Filed under: TokenAndPermUserStore

Identity Insert and SQL Server 2005 Import Export Wizard

Have you ever run the SQL 2005 Server Import Export Wizard and noticed the identity values for the destination table are renumbered? The SQL 2005 Server Import Export Wizard setting "Enable identity insert" is ignored when selecting "Optimize for Many Tables" causing the identity values at the destination to be renumbered.

The "Optimize for Many Tables" creates a SSIS package transferring the tables one-at-a-time within a For...Each looping structure. It does not include the logic to enable identity insert. Not selecting "Optimize for Many Tables" creates a SSIS package which transfers tables in parallel.

If you have many tables to transfer and you are concerned about server performance, don't select "Optimize for Many Tables" but instead set the Package property MaxConcurrentExecutables. You may need a couple of attempts to find the balance between performance and server load.

Hope you found this helpful.

All posting are provided "AS IS" with no warranties, and confers no rights.

Posted Saturday, June 24, 2006 3:12 PM by chrissk | 8 Comments

SSIS Data Flow Task An OLE DB error has occurred. Error code: 0x80040E37.

PROBLEM:

When using OLE DB Source Editor, the drop down "Name of the table or view" shows " doublequotes around the schema and table name. Selecting the table (for example "dbo"."Table1"), then Preview returns the error below:

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for ""dbo"."Table1"" failed. Check that the object exists in the database.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
   at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
   at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)

RESOLUTION:

SSIS by default will use " doublequotes as the quoted identifier if the OLE DB Provider does not override these settings. Change SSIS OLE DB for SQLCommand and type "select * from dbo.table" instead of picking the table from the list.

When using Sybase OLE DB provider, run the Sybase Configuration Manager and change the data source to "Enable Quoted Identifiers" to True (1) to suppot " doublequotes around the schema and table name.

Posted Monday, June 12, 2006 11:03 AM by chrissk | 2 Comments

HRESULT: 0xC0202022 An error occurred due to no connection. A connection is required when requesting metadata.

PROBLEM: If you are using Sybase as a OLEDB Source the list of tables can appear in the "Name of the Table or the View" drop down on the OLE DB Srouce Editor even if SSIS is in "offline". Once you select a table, then try to view the columns you get the following error:

Error at Data Flow Task [OLE DB Destination [1]]: An error occurred due to no connection. A connection is required when requesting metadata.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC0202022 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()

at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()

at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)

RESOLUTION: On the tool bar select SSIS, then clear "Work Offline".

http://blogs.msdn.com/photos/chrissk/images/628270/original.aspx

Posted Monday, June 12, 2006 10:46 AM by chrissk | 0 Comments