SQL 2005 SP2 Enterprise Slowness

Question

Post reply

SQL 2005 SP2 Enterprise Slowness

jason.nodarse

Ten Centuries

Points: 1143
More actions
June 30, 2010 at 8:50 am

#141148

Yesterday I had an outage on my SQL 2005 SP2 Enterprise server. Here are the symptoms.
1. I was unable to connect to the database using SSMS on occasions. Trying to connect will timeout.
2. When I was finally able to get into SSMS when I selected a database it would take a long time before it would expand.
3. When I ran a simple SELECT FROM query the query was taking 20 times as long as it should to return the data.
First I checked the system resources on the server and the CPU was at 4% and the RAM was low.
I then checked the SQL logs and did not see anything that caught my attention.
I then checked the event viewer and did not see any major errors in the application or the system logs.
At that point I restarted the SQL service and that did not solve my issue either.
So my last resort was to restart the server. After restarting the server I then was able to connect to SSMS and open everything normally. The SELECT statement was returing normally and the applications connecting to the databases started working again.
Does anyone have a clue of what could have happened here. Or has anyone had the same experience with SQL?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

blue_inelush Ten Centuries Points: 1118 More actions · Answer 1

Hello there,

Is there any configuration (mirroring, clustering) set on your server?

I suppose you have on your server an antivirus. Have you checked its process or if there are excluded/included the database files or the related sql server processes?

I'm asking you this because we had problems with some servers because of our antivirus program (bitdefender). And the strange thing was the same as yours... Nothing appeared to be wrong, but if connected by console on server, bitdefender errors raised.

Wish you good ideas! 🙂
Andreea

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

High CMEMTHREAD waits?

Do these describe your symptoms?

http://sqlinthewild.co.za/index.php/2008/05/02/token-and-perm-user-store-growth/

http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 3

I will look into this. Is this the command that needs to be issued when I am having the issue?

(DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’))

If yes then what exactly will be effected when I run this command? Will it just flush what is in memory and not cause any other databases to flake out?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Yup, that's the command. Don't run it unless you have the symptoms of the problem (high CMEMTHREAD waits, large token cache). iirc the query to monitor the cache is on one of those blog posts.

It's cached security tokens. SQL caches them because it's faster to lookup from cache than to check the permissions each time they're requested. The cache is instance-wide, not per database. Flushing it has no reall effects, queries just recheck permissions when they run.

If it is large (couple GB+), it'll take time to clear it and no queries can run while the cache is being cleared.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 5

Can you please type out the command for me seeing I get a syntax error when trying it on a test server I have? Also what command can I run to see if the CMemThread memory is being maxed out?

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 6

OK I was able to get the TSQL command to work but what can I run to see the current consumed memory that is cached?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

jason.nodarse (7/1/2010)
Also what command can I run to see if the CMemThread memory is being maxed out?

There's no such thing as CMEMTHREAD memory. CMEMTHREAD is a wait type. Check sys.dm_exec_requests for what current queries are waiting on, check sys.dm_os_wait_types for overall wait times since SQL started.

OK I was able to get the TSQL command to work but what can I run to see the current consumed memory that is cached?

What T-SQL command did you run? To see the token store memory, you need to run a query like this

select SUM(single_pages_kb + multi_pages_kb)/1024.0 AS SizeInMB

from sys.dm_os_memory_clerks

where [name] = 'TokenAndPermUserStore'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 8

Here is the results after running that command on my server.

2.687500

Is this high enough to cause an issue. I have 32gig of RAM on the server and I allow SQL to use 18 gigs.

I am also running SQL in a Active / Passive Cluster

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

No, that's 2 MB. Problems (in my experience) start around 1 GB. Do you have queries waiting on CMEMTHREAD? If not, then you have a different problem

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 10

How can I determine if any queries are waiting on CMEMTHREAD? Is there a command I can run to see this? If yes can you please let me know?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

As I mentioned before, query sys.dm_exec_requests. The particular column you're looking for is wait_type

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 12

Ok, I do not think this is my issue but it is good to know.

Thanks again for all your information!

Paul White SSC Guru Points: 150467 More actions · Answer 13

It's often impossible to diagnose the cause after you have restarted the server. If it happens again, try to connect via the DAC and gather as much information you can from DMVs like sys.dm_os_waiting_tasks and running queries like the one provided by Gail. If you modify that to the following:

SELECT name, SUM(single_pages_kb + multi_pages_kb)/1024.0 AS SizeInMB

FROM sys.dm_os_memory_clerks

WHERE [name] IN (N'Object Plans', N'SQL Plans', N'TokenAndPermUserStore')

GROUP BY [name]

ORDER BY SizeInMB DESC;

You'll also be able to see if ad-hoc procedure plans ('SQL Plans') are using too much memory or not - another possible cause of the symptoms you saw (though there are many possibilities).

My first advice though, would be to upgrade from SP2 as soon as possible.

See http://blogs.msdn.com/b/sqlreleaseservices/archive/2009/10/08/end-of-service-pack-support-for-sql-server-2005-sp2-and-sql-server-2008-rtm.aspx.

There are many important post-SP2 (and post-SP3) fixes which you are not benefiting from.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi