Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hash warnings Expand / Collapse
Author
Message
Posted Sunday, May 12, 2013 3:21 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 233, Visits: 919
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
Post #1451942
Posted Monday, May 13, 2013 4:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 15,498, Visits: 27,882
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452037
Posted Monday, May 13, 2013 5:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 233, Visits: 919
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
Post #1452047
Posted Monday, May 13, 2013 6:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 15,498, Visits: 27,882
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452062
Posted Monday, May 13, 2013 8:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 233, Visits: 919
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
Post #1452133
Posted Monday, May 13, 2013 9:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 15,498, Visits: 27,882
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452168
Posted Tuesday, May 14, 2013 5:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 233, Visits: 919
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 ?
Post #1452520
Posted Tuesday, May 14, 2013 6:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 15,498, Visits: 27,882
PearlJammer1 (5/14/2013)
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 ?


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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452527
Posted Tuesday, May 14, 2013 7:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 233, Visits: 919
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 ?
Post #1452549
Posted Tuesday, May 14, 2013 7:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 15,498, Visits: 27,882
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452552
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse