April 2010 - Posts

Interesting SSMS issue with waittype of PREEMPTIVE_OS_LOOKUPACCOUNTSID

Saw a recent issue with SQL2008 sp 1 where SQL Server Management Studio (SSMS) appeared to hang when a DBA expanded the database users tab of a database… ie like this :-

 image

When we looked at the waittype of the SSMS session - via sys.dm_os_waiting_tasks– it was waiting on PREEMPTIVE_OS_LOOKUPACCOUNTSID.

I’d not come across this waittype before, and there was very little information out on the web for it..

Looking at this issue using SQL Profiler – it them became apparent that SSMS was waiting on the function SUSER_SNAME.

I did a quick test using SET STATISTICS TIME to prove it was the SUSER_SNAME function causing our issue :-

SELECT * FROM sys.database_principals   -- completes in 6 milliseconds

SELECT SUSER_SNAME(sid),* FROM sys.database_principals  -- completes in 188941 milliseconds.

suser_sname validates a sid against Active Directory..

What I haven’t mentioned so far is that the database in question had been restored from a server on a different domain. This domain was untrusted from the current domain. However, it appeared that SSMS was still trying to validate the Windows login of each user when displaying the users list…. and taking a long time to return NULL for each user from the untrusted domain.

When all users that had a login from the untrusted domain are removed from the database – this fixed the issue.

To find out the users that had this issue :-

SELECT name FROM sys.database_principals WHERE type_desc IN(‘WINDOWS_USER’, ‘WINDOWS_GROUP’) and SUSER_SNAME(sid) IS NULL

Although I have not raised this as a case with MS – I do think this is a bug in SSMS – I do not see why windows logins should need to be validated when displaying only user details..

Posted by steveh99999 | 1 comment(s)
Filed under: ,

OBJECT_Name parameters and dbid

If you've been using SQL Server for a long time, you may have been used to using the OBJECT_NAME system function in the past - especially useful when converting table IDs into table names when querying sysobjects and sysindexes..... However, if you're an old-school DBA  - did you know since SQL 2005 service pack 2 it  accepts a  second parameter ?

database_id..

For example, this can be used to summarize some useful information from sys.dm_exec_query_stats.

When reviewing SQL Server performance - it can be useful to look at the most heavily used stored procedures rather than inefficient less frequently used procedures. 

Here's a query to summarize performance data on the most-heavily used stored procedures across all databases on a server  :-

SELECT TOP 20 DENSE_RANK() OVER (ORDER BY SUM(execution_count) DESC) AS rank, OBJECT_NAME(qt.objectid, qt.dbid) AS 'proc name', (CASE WHEN qt.dbid = 32767 THEN 'mssqlresource'

ELSE DB_NAME(qt.dbid)

END ) AS 'Database',

OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) AS 'schema',

SUM(execution_count) AS 'TotalExecutions',SUM(total_worker_time) AS 'TotalCPUTimeMS',

SUM(total_elapsed_time) AS 'TotalRunTimeMS',

SUM(total_logical_reads) AS 'TotalLogicalReads',SUM(total_logical_writes) AS 'TotalLogicalWrites',

MIN(creation_time) AS 'earliestPlan', MAX(last_execution_time) AS 'lastExecutionTime'

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE OBJECT_NAME(qt.objectid, qt.dbid) IS NOT NULL

GROUP BY OBJECT_NAME(qt.objectid, qt.dbid),qt.dbid,OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid)

 

 

 

Too much I/O in the morning ?

Interesting little improvement on a SQL 2005 system I encountered recently…..

Some background - this system had a fairly ‘traditional OLTP’ workload ie  heavily used during day – till around 9pm, then had a batch window for several hours, then not much activity in the early hours of the day, until normal workload resumed the following morning.

Using perfmon, I noticed that every morning, we would see a big spike in SQL Server I/O when the application started to be used...

As it was 2005 I decided to look at what tables were in cache before and after the overnight batch processing ran… ( using DMV equivalent of dbcc memusage that I posted earlier).

Here’s what I saw :-  

Picture830 

So, contents of data cache split fairly evenly between my 'important/heavily used' tables. 

 After this:- some application batch processing,backups, DBCC checks and reindexes were run.  A fairly standard batch I'd suggest.

Cache contents then looked like this :-

Picture230

Hmmmm – most of cache is now being used by a table I’ve described as ‘unimportant’. Why ?

Well, that table was the last to be reindexed…. purely due to luck, as  the reindexing stored procedure performing a loop in alphabetical order through all application tables... 

When the application starts to be used again – all this ‘unimportant’ data has to be replaced in cache by data that is heavily used…

So, we changed the overnight reindex scripts –  the most heavily accessed tables are now the last to be reindexed.

Obvious really, but we did see a significant reduction in early-morning I/O after changing the order of our reindexing.

 

Posted by steveh99999 | with no comments
Filed under:

DBCC MEMUSAGE in 2005/8 ?

I used to like using undocumented command DBCC MEMUSAGE in SQL 2000 to see which tables were using space in SQL data cache.

In SQL 2005, this command is not longer present. Instead a DMV – sys.dm_os_buffer_descriptors – can be used to display data cache contents,  but this doesn’t quite give you the same output as DBCC MEMUSAGE.

I’m also aware that you can use Quest’s spotlight tool to view a summary of data cache contents.

Using  this post by Umachandar Jayachandran  of Microsoft, I was able to create the following equivalent for SQL 2005/8. I’ve wrapped Umachandar’s original query in a CTE to produce summary information :-

;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE  bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 20 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC

I’m not 100% happy with the results of the above query however… I’ve noticed that on a busy BizTalk messageBox database  it will return information on pages that contain GHOST rows – . ie where data has already been deleted but has yet to be cleaned-up by a background process – I’m need to investigate further why cache on this server apparently contains so much GHOST data…

For more information on the background ghost cleanup process, see this article by Paul Randall.

However, I think the results of this query should still be of interest to a DBA. I have another post to come shortly regarding an example I encountered where this information proved useful to me…

I notice in SQL 2008, sys.dm_os_buffer_descriptors gained an extra column – numa_mode – I’m interested to see how this is populated and how useful this column can be on a NUMA-enabled system. I’m assuming in theory you could use this column to help analyse how your tables are spread across Numa-enabled data-cache ?

Posted by steveh99999 | 4 comment(s)
Filed under: ,