May 12, 2013 at 3:21 pm
Hi - Can anbody advise me on the following:
I am using red gates sql monitor and every night during the nightly etl loads I am getting the hash warnings alert triggered - it is set to fire when the value reaches 50. I have read that maybe adding indexes, or adding more memory may help.
How do i find out what job is causing this and/or what queries are running when this happens ?
I'm presumiing i will have to set profiler to run all night long from say 12am to 7 am using filters. I just want to capture the relevant info without adding extra overhead to the server, so can anybody give some tips on the best way to approach this ?
Thanks:-)
May 13, 2013 at 4:24 am
You could turn on profiler or, better still, extended events to capture the query executions that are occurring. But, there's a way to look at what was going on at the time on the server using SQL Monitor. On the main screen, in the upper right, is a clock. You can click on that to rewind time. You can go back to the moment when the problem is occurring and then look at the longest running queries or the queries using the most resources. That should help you identify the problem pretty quickly without having to set up extended events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2013 at 5:12 am
Thank you very much for that info Grant - I'll be sure to check that out and get back to you if i have any further questions !!
Thanks very much:-)
May 13, 2013 at 6:01 am
Not a problem. Happy to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2013 at 8:29 am
Grant -Once i select the time to the desired time, the screen changes to show the following:
(local)
CPU, MEMORY DISKS, NETWORKS, PROPERTIES AND SYSTEM PROCESSES (TOP 10).
Can you guide me on how i drill down to the query that was running at the time that would cause the issue ?
I have had another screen up whereby i get the top 10 expensive quieres and it says i can change which ones it displays by clicking the drop down arrow (eg change it to the last 8 hours). I guess what im asking here is when i select the time on the clock to 5:45am does this not display the processes/queries that where running.
Thanks
Steve
May 13, 2013 at 9:20 am
When you select the time in the past, it changes the screen as you see. Next, you can click on the instance inside the server. If you do that, at the top of the screen, well, below the tool bar and the timeline, you should see the instance name and a list of databases. You can scroll down on that screen to see the list of top queries for the time in question. You can look at duration or resources used. To exactly nail it down, you may need to move the time to just after the queries completed. It usually takes a little investigation. Moving the time just requires you to bump it forward or back using the buttons at the top of the screen.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2013 at 5:43 am
Hello again Grant
Heres what i am finding.
The alert for hash warnings is showing up against server 15 and in the Master database.
I click on 'overview', then my server (server15), then 'local' and that brings up the databases on the server, then i click on 'master' and change the time to the required time when the alert happened (in this case 04:28).
When i look at the query text at the bottom the first query is this :
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL
THEN 'SQLServer'
ELSE 'MSSQL$'
+ CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)
END + ':SQL Errors'
AND instance_name = 'User Errors'
From query plan: 0x060001001847c40140a18482000000000000000000000000
*****************************************************************
Another one of the queries reads:
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Pages'
AND instance_name = 'SQL Plans';
From query plan: 0x060001009877e62440a1a082000000000000000000000000
The querys dont seem to correlate to the things we run - How are these being generated ?
May 14, 2013 at 6:06 am
PearlJammer1 (5/14/2013)
Hello again GrantHeres what i am finding.
The alert for hash warnings is showing up against server 15 and in the Master database.
I click on 'overview', then my server (server15), then 'local' and that brings up the databases on the server, then i click on 'master' and change the time to the required time when the alert happened (in this case 04:28).
When i look at the query text at the bottom the first query is this :
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL
THEN 'SQLServer'
ELSE 'MSSQL$'
+ CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)
END + ':SQL Errors'
AND instance_name = 'User Errors'
From query plan: 0x060001001847c40140a18482000000000000000000000000
*****************************************************************
Another one of the queries reads:
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Pages'
AND instance_name = 'SQL Plans';
From query plan: 0x060001009877e62440a1a082000000000000000000000000
The querys dont seem to correlate to the things we run - How are these being generated ?
Those queries are being run by SQL Monitor. Neither is especially egregious in any way (and we have a couple of queries I'm not happy with, but not these). Other than the fact that they're not your queries, were these using excessive amounts of resources or running for long periods of time?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2013 at 7:01 am
Grant,
The query with the largest numbers against it is the second one in the list:
INSERT INTO @MemStat
EXEC ('DBCC memorystatus() WITH tableresults');
From query plan: 0x06000100e090292b40e18282000000000000000000000000
*****************************************************************
Thje metrics are:
execution count = 4, duration(ms) = 401, cpu time(ms) = 399, physical reads = 0, logical reads = 1858, logical writes = 0
These are the metrics next to the tsql text - do i need to look at anything else ?
May 14, 2013 at 7:06 am
PearlJammer1 (5/14/2013)
Grant,The query with the largest numbers against it is the second one in the list:
INSERT INTO @MemStat
EXEC ('DBCC memorystatus() WITH tableresults');
From query plan: 0x06000100e090292b40e18282000000000000000000000000
*****************************************************************
Thje metrics are:
execution count = 4, duration(ms) = 401, cpu time(ms) = 399, physical reads = 0, logical reads = 1858, logical writes = 0
These are the metrics next to the tsql text - do i need to look at anything else ?
Accumulated execution time then is 400ms or less than 1/2 of 1 second. I suspect pretty strongly that this can't be the problem. You probably need to explore forward or backward in time to see what else is going on. I'd be seriously shocked if a query taking 100ms is hurting your server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2013 at 9:10 am
Grant
Thanks for all your help i know at least have a better understanding of how to drill down using sql monitor.
I have traced the problem to some sharepoint reports that the datawarehouse guys start to run -
We might have to add more memory for this server as it only has 24gb (it alls in the cloud and quite cost wise shouldnt expensive !) and hopefully this will help
Once again thanks for your time.
Steven
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply