January 13, 2014 at 1:59 pm
I am running simple queries would barely rise above 10-15% CPU and it appears are now pushing 75+% to 100% to the point where the remote desktop is freezing for seconds at a time before updating. What can be the issue?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 13, 2014 at 3:26 pm
This could be a complicated issue so you want to start at the simplest point. Generally with databases if the CPU is 100%+ that means that RAM is being filled and the pagefile/CPU is being accessed heavily. There could be other issues if the system is part of a virtual cluster, if it has other applications running as well etc. If you get a chance to reboot the server and it's back to normal then it would point to system resources, namely RAM.
Check all system and SQL server logs.
January 14, 2014 at 4:13 am
Generally it's not a good idea to RDP into a production server, especially to just run queries. Remote connections through SSMS on your desktop are safer.
But, that's probably not the issue. I'd suggest looking at sys.dm_exec_requests to see if there is blocking going on and to understand exactly how much resources your queries are using. A "simple" query that uses 15% of CPU is not one that I would consider simple. Maybe your query needs tuning. You can also look at sys.dm_os_wait_stats to understand what waits are being experienced on the server. You can even reset that object before and after querying it to see what changed during a query (but it resets it for the server, so be sure you want to go there).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2014 at 6:45 am
This is the Query that I was running and it executed in 32 seconds. I checked the index fragmentation as well and its only 10% fragmented. Is it safer for me to right click on the query and then click on Display estimated execution plan?
SELECT fct.AccountID, count(*)
FROM fctAssetFulfillment fct
JOIN FulfillmentPartnerInstall fpi ON fpi.FMProBoxID = fct.AMC_BoxID
WHERE fct.Source = 'FMPro' AND isnumeric(fct.AMC_BoxID)<>0
AND fct.SerialNo = fpi.SerialTag
AND fpi.CompletionDate IS NOT NULL
AND fpi.AssetUsage LIKE '%install%'
AND fct.InstallActualDate<>fpi.CompletionDate
--AND (fct.FulfillmentStatus <> 'installed')
GROUP BY fct.AccountID
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 14, 2014 at 10:52 am
New Born DBA (1/14/2014)
This is the Query that I was running and it executed in 32 seconds. I checked the index fragmentation as well and its only 10% fragmented. Is it safer for me to right click on the query and then click on Display estimated execution plan?SELECT fct.AccountID, count(*)
FROM fctAssetFulfillment fct
JOIN FulfillmentPartnerInstall fpi ON fpi.FMProBoxID = fct.AMC_BoxID
WHERE fct.Source = 'FMPro' AND isnumeric(fct.AMC_BoxID)<>0
AND fct.SerialNo = fpi.SerialTag
AND fpi.CompletionDate IS NOT NULL
AND fpi.AssetUsage LIKE '%install%'
AND fct.InstallActualDate<>fpi.CompletionDate
--AND (fct.FulfillmentStatus <> 'installed')
GROUP BY fct.AccountID
You have several issues there that are going to lead to pretty long running queries. First, ISNUMERIC on a column like that will absolutely lead to scans. You can't take advantage of any indexes on that table. Also LIKE with a starting wild card, '%install%', will also lead to scans, no index use. Also all the OR statements, <>, may prevent index use as well. This is likely to be a query that causes long execution times and excessive system load. That doesn't even get into whether or not you have indexes in place that can help support what you're trying to do with the query.
After you address the immediate issues, you can look at the execution plan to get an idea of how the optimizer is going to resolve it, whether or not you have good, selective indexes, your statistics are up to date, etc.
Fragmentation isn't part of the problem.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2014 at 11:15 am
Thank you all, I will make some changes in that query and will run it again. Lets see what happens.
Thanks again.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply