We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration:

Windows server 2003 R2, SP2, Enterprise Edition, 16 GB RAM , 12 CPU'S 32 Bit

SQL server 2005, SP4, Enterprise Edition, 32 Bit.

We have already informed business on the upgrade to 64 bit which would take over a month.

But for the current issue, we are trying to gather the data if we can resolve the memory pressure or finally come to a conclusion to increase RAM.

Action Completed: Re-indexing and update stats are proper for this DB.

As shown below, we have been noticing the semaphore waittype for past 5 days, ran during the load hours:

Few info after below queries: size of buffer= 137272

SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'

and semaphore memory= 644024 per below query

 SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores

Below is some more info gathered from dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants dmv's

So from above info gathered and per SP_Blitz data Resource semaphore seems to be the problem.

Is memory 'target_memory_kb' assigned for resource semaphore id's too low, as compared to 16 GB RAM available.

Note* per analysis on 8 hours run 'target_memory_kb' is always under 1 GB, compared to 16 GB available?

what could be the issue here and how to resolve, please suggest

Thanks

Hi,

You have 32 bit system have you enabled AWE and does SQL Server service account has Locked pages in memory privilege ?

Why have you set MAXDOP to 1 ?

If you see output of memory_grants DMV you can see large amount of queries requesting 2 MB of memory but actualy at that time using around avrage of 500-600KB. So why is your query requesting 2 MB and only reason i can think is

1. Query is written poorly

2 Query is making wrong estimate.

With 32 bit system its difficult to manage database which is 300+G. You are bound to face memory issue. Can you clear the wait stats and start collecting data again. Well clearing would surely have issue on query performance but I could see resource semaphore as just 4 % of top wait.

Are you seeing blocking ? deadlocks ?

Hello @Shanky,

Yes we have AWE enabled and SQL service account  is added under user right assignment 'lock pages in memory' Policy.

MAXDOP was set to 1 by the previous TM, I've changed that to  4.

yes, you are correct that was my concern as well, when we have 16 GB memory and targeted is 1 GB, compared to 2 MB its granted. We checked for stats, rebuild indexes , missing indexes , as of now all good.

Only thing we found from SP_Blitzindex is 2 aggressive indexes and about 50 duplicate indexes.

For blocking, we kept counters like memory grants pending, but monitored for a day and hardly see its value above 0.

I have collected the latest wait stats for today, below is the image for same.

(Just to add: this is a server for reporting purpose only and is subscriber for the DB (same 300 GB) on OLTP server)

I have collected the latest wait stats for today, below is the image for same.

(Just to add: this is a server for reporting purpose only and is subscriber for the DB (same 300 GB) on OLTP server)

Well if memory grant is not pending I won't upfront say its memory issue. Even I was guessing the same so asked you to clear buffer cache and get fresh stats.

The stats have changed now as per above I would say its CPU pressure I can see top wait as SOS_scheduler_yield and CXPACKET. Or may be you are running CPU intensive queries.

Now are you facing Blocking? Serious slowness .....

Thanks Shanky,

I've cleared the wait stats, and buffer cache. Will analyse for some time and let you know.

Yes and that's right, we have a query ( which is developed as a part of filter) used in ssrs reports, which runs with almost 20 different proceed id'f for almost 15 hours. Status always shows runnable. Reports running from reportingserver vary but when they hit this server, those reports can never fetch data and get time out.

Also, at that time CPU usage lies between, 90-95 % always.

From the reports we don't see any blocking as such, but yes serious slowness for the reports for sure.

please help on how to proceed from here.

You have significant pageiolatch and async_network_io, these are "real" waits.

What about your perfmon counters, page life expectancy, seconds/read, queue lengths?

OTOH if your CPU is high, you have other issues.

Upgrading your server to 64bit will probably help A LOT, it typically has MUCH higher CPU performance and hey, maybe it will come with a few SSDs to help things along as well?

But just as a SWAG on the numbers, if you have a 300gb database, you're doing reporting, and have only (!) 16gb RAM, odds are things would go a *lot* faster with more RAM, I mean at least 64gb and better yet 250gb.  RAM is cheap and covers a multitude of sins.

Josh

• Edited by JRStern 15 hours 6 minutes ago

16gb RAM, odds are things would go a *lot* faster with more RAM, I mean at least 64gb and better yet 250gb.  RAM is cheap and covers a multitude of sins.

Josh did you noted OP has 32 bit edition and probably 250 G is not possible and 64G can be but how would SQL Server 23 bit with PAE enabled use 64 bit is still difficult to predict

Yes and that's right, we have a query ( which is developed as a part of filter) used in ssrs reports, which runs with almost 20 different proceed id'f for almost 15 hours. Status always shows runnable. Reports running from reportingserver vary but when they hit this server, those reports can never fetch data and get time out.

Also, at that time CPU usage lies between, 90-95 % always.

From the reports we don't see any blocking as such, but yes serious slowness for the reports for sure.

please help on how to proceed from here.

Have you performed query and index tuning on the long-running queries?  Perhaps the previous DBA reduced MAXDOP specifically to prevent the problem process from monopolizing CPU resources and impacting other users.

Yes Dan,

I've performed index tuning for those queries, like missing index , rebuilds and stats update.

So is it going to be more worst, after that change of MAXDOP ?

However, concern would be this one query, which as per developer is just a part of filter in ssrs reports, i.e. small query for the larger query has multiple sessions open for almost a day now.

HOst name for all of them is common, program is .net.

Also, @Shanky for blocking, i am seeing replication process which updates the DB from publisher blocking the above multiple sessions query. I am not sure but do replication(seeing an update) causes blocking.

Adding more to the complication-- this update replication process is blocked by reindexing and update stats job.

and above indexing job which seems running from yesterday is blocked by multiple sessions query as mnetioned above.

Josh did you noted OP has 32 bit edition and probably 250 G is not possible and 64G can be but how would SQL Server 23 bit with PAE enabled use 64 bit is still difficult to predict

Sure, but he says they are upgrading to 64bit, that's the one where he could add the RAM.

AWE/PAE RAM on 32bit is still slow, compared to 64bit, so that upgrade is absolutely the best first move.

Josh

HOst name for all of them is common, program is .net.

Also, @Shanky for blocking, i am seeing replication process which updates the DB from publisher blocking the above multiple sessions query. I am not sure but do replication(seeing an update) causes blocking.

Adding more to the complication-- this update replication process is blocked by reindexing and update stats job.

You are not giving complete information, you are giving it piecemeal why you did not told us about replication before. I would say segregating report server and running reports there would also be good option