Hash warnings

  • 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:-)

  • 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

  • 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:-)

  • 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

  • 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

  • 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

  • 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 ?

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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 ?

  • 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

  • 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