SQL Server Performance

SQL server performance issues.      We have an application that uses SQL.   During the day this application can get very slow.    Something that loads in less than a seconds after hours can take 4-5 seconds during the day.     In talking with the developers they added special logging to there application to determine where the delay was.    They claim that it is while querying data from SQL.    This would imply that SQL is overloaded or bottle necked somewhere.    After literally weeks of troubleshooting I cannot find anything.   Network is under 10%..   memory usage is normal ... CPU is normal.     I just can't find a reason for the slowness.      Is there anything further I can do to help pinpoint the issue.     I even tried SQL monitoring software free trial for 2 weeks and it reported no issues.
LVL 2
Zoldy2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< They claim that it is while querying data from SQL. >>

 It still could be the application as the queries may be inefficient.   They also may be pulling data over the wire and processing it client side rather than letting the server do the work.

 It could also be the DB's design with users blocking one another, or a lack of proper indexing.    Some more details on the app (i.e. what's it written in, how many users, is this a standard commercial app running elsewhere OK or is it totally custom, etc) would help.  Also some info on the server; number of users, amount of memory, some details on the database (size, records, etc).

 Also is this an old or new problem?

 But on the basics first; you say CPU, Memory, and network are normal, exactly what are those numbers?    Many servers once they reach 90% of memory utilization will slow down considerably.   Since SQL server will try and consume as much memory as possible, things can get out of control.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zoldy2000Author Commented:
I will tell you as much as I can about the application but I am unable to tell you specifics about the code.     In my view it is very small and that is why the delays are a bit of a surprise.   its always been a slow application and as we use more modules its just gets slower.    Meaning its been slow all along.      We are only license for 25 concurrent users.    The SQL is dedicated.     The Web interface is on a dedicated web server for remote clients.    And the windows clients are deployed on 3 load balanced remote desktop servers.    in my view extreme overkill and we did this to try to resolve the speed issues.   In my view its the application itself but I am trying my best to prove this to the developers who blame the environment.    They use test servers to show me the system is fast.     One user logged on and everything installed locally.    Not a fair assessment but also difficult for me to argue with no data.      The SQL server is virtual and I have assigned 16 Gig of Ram.    12 of that dedicated for SQL.    There server runs with about 2.5 gig free at any given time.     CPU bounces between 1-12% ... never seems to peak any higher during the slow periods.      Network is 1% utilized on average as well.

Any virtual environment things I need to consider.    Any benefit to adding more RAM ?    It's seems I have lots.     Any way to measure this other than how I have done already?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< its always been a slow application and as we use more modules its just gets slower.>>

 Ask the developers what it is written in and where data is processed (client side or server side).

 It sounds like you have adequate resources, but it is possible that you have some network issues going on even though that appears normal.

  A few other things that are basic checks:

1. Verify that the SQL server has plenty of free disk space.
2. Take a look at the event logs for the server that SQL server is on.  Look at the application and system logs.   Look for errors, critical events, and warnings.
3. Take a look at SQL Server logs.   Again looking for anything that sticks out that doesn't look normal.

After that; is maintenance on the database being done on a regular basis?   ie.  updating statistics, re-organizing indexes, etc?

Assuming so and that nothing seems to be wrong with the server, then the next step I'd take is using SQL Server Activity monitor, which is in SQL Server Management Studio, and watch the activity while the database is being used.

 Under processes, your looking for ones that are getting blocked by someone else.   If that happens routinely, it can lead to long wait times.    Under "Resource waits", what's the top couple of items?    and in the bottom section "Recent expensive queries", look for the same query, or queries in the same area of the database.

  What your looking for here is to get a general sense of where hold-ups are and again looking for anything that sticks out.

Jim.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Zoldy2000Author Commented:
Data is processed client side.   looking into the rest of the suggestions some I have done some I have not and will reply back.
0
Zoldy2000Author Commented:
1. Verify that the SQL server has plenty of free disk space.
yes plenty

2. Take a look at the event logs for the server that SQL server is on.  Look at the application and system logs.   Look for errors, critical events, and warnings.
none

3. Take a look at SQL Server logs.   Again looking for anything that sticks out that doesn't look normal.
looks normal

After that; is maintenance on the database being done on a regular basis?   ie.  updating statistics, re-organizing indexes, etc?
yes done weekly


I ran the activity monitor while running one of the slowest things in the app.   i admit though I am having trouble uderstanding what it is telling me

Under processes, your looking for ones that are getting blocked by someone else.  
Do not know how to see if they are getting blocked?
 

Under "Resource waits", what's the top couple of items?    
network I/O is the big one which I had read before can be because of the application not the network that this is misleading

bottom section "Recent expensive queries", look for the same query, or queries in the same area of the database.
the big ones seem to be for tempdb and msdb so not sure what that means?   Also not sure if I need to sort this list to find the worst?
there are many results.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
How big is the database?
Which SQL Server Edition are you using?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Do not know how to see if they are getting blocked?>>

  There is a column called "Blocked" and it will tell you the process that is blocking you.

<<network I/O is the big one which I had read before can be because of the application not the network that this is misleading>>

 It can and can't.  For example, if the packet size between SQL and the client is small, it can take many packets to get information across rather than one or two large ones.


<<Data is processed client side. >>

 Well that can be a problem if your not careful there.  The application should be using Views, Stored Procedures, and Triggers to push as much as possible server side.

 So is this a standard commercial app or a custom one?  And what's it written in?

Jim.
0
Zoldy2000Author Commented:
13 Gig
SQL server 2008
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have a database of 13GB but only 12GB of RAM for the SQL Server. Isn't necessary a bad thing but depending on the database usage, it should be better to increase the RAM.

I've asked about the SQL Server Edition (Express, Standard, Enterprise,...) and not version. But being a 13GB database then Express Edition is out of question.
0
Zoldy2000Author Commented:
It is a standard app but its known to not be written optimal.    However not to the degree we are seeing which is why I am doing all of this.    I do not know the language.    I have heard others talk about the way they call the data is not optimized for SQL whatever that might mean.

Nothing in the blocked by column.

Although While looking I notice one with a very high wait time.

The application is DatabaseMail?    Task state suspended ... is that normal?
0
Zoldy2000Author Commented:
Also worth noting in the activity monitor all the processes with large wait times the task state is suspended.. again not sure how to translate this data though.
0
Zoldy2000Author Commented:
Sorry it is SQL Standard.    And I can increase RAM easily
0
Zoldy2000Author Commented:
FYI.. I figured out how to reduce the log database.     So that is cleared up now
0
Scott PletcherSenior DBACommented:
Here's quick queries to do snapshot checks on schedulers, memory and CPU.  I don't think it's any of these, but need to verify that first before looking at indexes, the most likely culprit.

If you want me to follow-up with index analysis, just let me know.


SELECT scheduler_id, current_tasks_count, runnable_tasks_count,
    CASE WHEN runnable_tasks_count < 5 THEN 'OK'
         WHEN runnable_tasks_count < 9 THEN 'Potential problem.'
         ELSE 'Problem!' END AS status
FROM  sys.dm_os_schedulers
WHERE scheduler_id < 255

SELECT system_memory_state_desc,
    total_physical_memory_kb, available_physical_memory_kb,
    total_page_file_kb, available_page_file_kb    
FROM sys.dm_os_sys_memory;

SELECT TOP (50)
    qs.total_worker_time / qs.execution_count AS average_CPU_used,
    qs.total_worker_time AS total_CPU_used,
    qs.execution_count AS execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2, (CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END -
        qs.statement_start_offset)/2) AS executing_query,
    qt.text AS full_query_text,
    DB_NAME(qt.dbid) AS db_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY average_CPU_used DESC;

/* CPU Usage by Db

;WITH DB_CPU_Stats AS
(
    SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
        SUM(total_worker_time) AS [CPU_Time_Ms]
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY CPU_Time_Ms DESC) AS row_num,
       DatabaseName, CPU_Time_Ms,
       CAST(CPU_Time_Ms * 1.0 / SUM(CPU_Time_Ms)
       OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 AND DatabaseID <> 32767 /*exclude system dbs*/
ORDER BY row_num OPTION (RECOMPILE);

*/
0
Zoldy2000Author Commented:
Am I supposed to post results or looking for something in the results?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also worth noting in the activity monitor all the processes with large wait times the task state is suspended.. again not sure how to translate this data though.
Suspended means waiting on something else. And I would suspect that's waiting for locks in an object to be released.
0
Scott PletcherSenior DBACommented:
Am I supposed to post results or looking for something in the results?

Mostly look for it.  The first one has a status column to help indicate potential issues.  The second has your top 50 cpu-using queries.  You can see if the first few stand out.

After looking for physical issues -- drive issues, significant RAM shortage or CPU shortage -- best thing is to comprehensively review indexes, esp. on the larger tables.  If you have major index issues, working on the code will be too long and still won't yield best results.
0
Zoldy2000Author Commented:
The results of the query are status ok on the first part.     Available memory is high on the second part.    The last part there are lots of results and I do not know what it is telling me.
0
Scott PletcherSenior DBACommented:
Run this code and post the results.

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY -8;

IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
    DROP TABLE dbo.#index_specs
IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
    DROP TABLE dbo.#index_missing
IF OBJECT_ID('tempdb.dbo.#index_usage')  IS NOT NULL
    DROP TABLE dbo.#index_usage

CREATE TABLE dbo.#index_specs (
    object_id int NOT NULL,
    index_id int NOT NULL,
    min_compression int NULL,
    max_compression int NULL,
    drive char(1) NULL,
    alloc_mb decimal(9, 1) NOT NULL,
    alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 2)),
    used_mb decimal(9, 1) NOT NULL,
    used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 2)),
    rows bigint NULL,
    table_mb decimal(9, 1) NULL,
    table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 2)),
    size_rank int NULL,
    approx_max_data_width bigint NULL,
    UNIQUE CLUSTERED ( object_id, index_id )
    )

DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @include_schema_in_table_names bit
DECLARE @table_name_pattern sysname
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.
DECLARE @debug smallint

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @list_missing_indexes_summary = 0
SET @include_schema_in_table_names = 0
SET @table_name_pattern = '%'
SET @order_by = -2
SET @format_counts = 3
SET @debug = 0

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

DECLARE @is_compression_available bit
DECLARE @sql varchar(max)

IF CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9%'
OR (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND 
    CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%')
    SET @is_compression_available = 0
ELSE
    SET @is_compression_available = 1

SET @sql = '
INSERT INTO #index_specs ( object_id, index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    min_compression, max_compression,' END + '
    alloc_mb, used_mb, rows )
SELECT 
    base_size.object_id, 
    base_size.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    base_size.min_compression,
    base_size.max_compression,' END + '
    (base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
    (base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
    base_size.row_count AS rows
FROM (
    SELECT
        dps.object_id,
        dps.index_id, ' +   
        CASE WHEN @is_compression_available = 0 THEN '' ELSE '
        ISNULL(MIN(p.data_compression), 0) AS min_compression,
        ISNULL(MAX(p.data_compression), 0) AS max_compression,' END + '    
        SUM(dps.reserved_page_count) AS total_pages,
	    SUM(dps.used_page_count) AS used_pages,
	    SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
    FROM sys.dm_db_partition_stats dps ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    INNER JOIN sys.partitions p /* WITH (NOLOCK)*/ ON
        p.partition_id = dps.partition_id ' END + '
    --WHERE dps.object_id > 100
    GROUP BY 
        dps.object_id,
        dps.index_id
) AS base_size
LEFT OUTER JOIN (
    SELECT 
        it.parent_id,
        SUM(dps.reserved_page_count) AS total_pages,
	    SUM(dps.used_page_count) AS used_pages
    FROM sys.internal_tables it /* WITH (NOLOCK)*/
    INNER JOIN sys.dm_db_partition_stats dps /* WITH (NOLOCK)*/ ON 
        dps.object_id = it.parent_id
    WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
    GROUP BY
        it.parent_id
) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_id
'
IF @debug >= 1
    PRINT @sql
EXEC(@sql)

UPDATE [is]
SET approx_max_data_width = index_cols.approx_max_data_width
FROM #index_specs [is]
INNER JOIN (
    SELECT index_col_ids.object_id, index_col_ids.index_id, 
        SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
    FROM (
        SELECT ic.object_id, ic.index_id, ic.column_id
            --,object_name(ic.object_id)
        FROM sys.index_columns ic
        WHERE
            ic.object_id > 100
        UNION
        SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
            --,object_name(i_nonclus.object_id)
        FROM sys.indexes i_nonclus
        CROSS APPLY (
            SELECT ic_clus2.column_id
                --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
            FROM sys.index_columns ic_clus2
            WHERE
                ic_clus2.object_id = i_nonclus.object_id AND
                ic_clus2.index_id = 1 AND
                ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
        ) AS ic_clus
        WHERE
            i_nonclus.object_id > 100 AND
            i_nonclus.index_id > 1
    ) AS index_col_ids
    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
    GROUP BY index_col_ids.object_id, index_col_ids.index_id    
) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

UPDATE ispec
SET table_mb = ispec_ranking.table_mb,
    size_rank = ispec_ranking.size_rank
FROM #index_specs ispec
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
    FROM (
        SELECT object_id, SUM(alloc_mb) AS table_mb, MAX(rows) AS rows
        FROM #index_specs
        GROUP BY object_id
    ) AS ispec_allocs        
) AS ispec_ranking ON
    ispec_ranking.object_id = ispec.object_id

IF @list_missing_indexes = 1
BEGIN
    SELECT
        IDENTITY(int, 1, 1) AS ident,
        DB_NAME(mid.database_id) AS Db_Name,
        CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
        ispec.size_rank, ispec.table_mb,
        CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
             WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
             WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
             ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
        CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id) + '.' 
             ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,        
        user_seeks, user_scans, cj1.max_days_active, unique_compiles, 
        last_user_seek, last_user_scan, 
        CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
        CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
        system_seeks, system_scans, last_system_seek, last_system_scan,
        CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
        CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
        mid.statement, mid.object_id, mid.index_handle
    INTO #index_missing
    FROM sys.dm_db_missing_index_details mid /*WITH (NOLOCK)*/
    CROSS JOIN (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
    ) AS cj1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig /*WITH (NOLOCK)*/ ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs /*WITH (NOLOCK)*/ ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    CROSS APPLY (
        SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    OUTER APPLY (
        SELECT ispec.table_mb, ispec.size_rank
        FROM dbo.#index_specs ispec
        WHERE
            ispec.object_id = mid.object_id AND
            ispec.index_id IN (0, 1)
    ) AS ispec
    WHERE
        1 = 1 
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name,
        CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
        Table_Name,
        equality_columns, inequality_columns,
        user_seeks DESC
    SELECT *
    FROM #index_missing
    ORDER BY ident
    IF @list_missing_indexes_summary = 1
    BEGIN
        SELECT 
            derived.Size_Rank, derived.table_mb,
            derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks, 
            ISNULL((SELECT SUM(user_seeks)
             FROM #index_missing im2
             CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds
             WHERE im2.Size_Rank = derived.Size_Rank AND
                 LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
            ), 0) AS Inequality_Seeks,
            derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
            derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
        FROM (
            SELECT 
                Size_Rank, MAX(table_mb) AS table_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column, 
                SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
                MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
                MIN(max_days_active) AS Max_Days_Active,
                MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
                (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
                MAX(ds.ItemNumber) AS Equality#
            FROM #index_missing
            CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
            WHERE equality_columns IS NOT NULL
            GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
        ) AS derived
        ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC        
    END --IF
END --IF



PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

-- list index usage stats (seeks, scans, etc.)
SELECT 
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME() AS db_name,
    --ispec.drive AS drv,
    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb, 
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.' 
         ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,    
    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
         WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
         WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
         ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
    ispec.table_gb, ispec.alloc_gb AS index_gb,
    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN '' 
         ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [Uniq?],
    REPLACE(i.name, oa1.table_name, '~') AS index_name,
    --fc_row_count.formatted_value AS row_count,
    i.index_id,
    ispec.approx_max_data_width AS [data_width], 
    CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
    key_cols AS key_cols,
    LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ',', '')) + 1 AS nonkey_count,
    nonkey_cols AS nonkey_cols,
    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
    ius.user_lookups, ius.user_updates,
    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
    dios.range_scan_count, dios.singleton_lookup_count,
    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
    DATEDIFF(DAY, CASE 
        WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date 
        WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date 
        ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,
    dios.row_lock_count, dios.row_lock_wait_in_ms,
    dios.page_lock_count, dios.page_lock_wait_in_ms,    
    ius.last_user_seek, ius.last_user_scan,
    ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    i.fill_factor,
    ius2.row_num,
    CASE 
        WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
        WHEN ispec.max_compression = 2 THEN 'Page'
        WHEN ispec.max_compression = 1 THEN 'Row'
        WHEN ispec.max_compression = 0 THEN ''
        ELSE '(Unknown)' END AS max_compression,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,
    GETDATE() AS capture_date
INTO #index_usage
FROM sys.indexes i /*WITH (NOLOCK)*/
INNER JOIN sys.objects o /*WITH (NOLOCK)*/ ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
) AS cj1
OUTER APPLY (
    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
        THEN 1 ELSE 0 END AS has_clustered_index
) AS cj2
LEFT OUTER JOIN dbo.#index_specs ispec ON
    ispec.object_id = i.object_id AND
    ispec.index_id = i.index_id
OUTER APPLY (
    SELECT STUFF((
    SELECT
        ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
    ), 1, 2, '')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT STUFF((
    SELECT
        ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(ic.object_id, ic.column_id)
    FOR XML PATH('') 
    ), 1, 2, '')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius /*WITH (NOLOCK)*/ ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats /*WITH (NOLOCK)*/
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys /*WITH (NOLOCK)*/
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
LEFT OUTER JOIN (
    SELECT *
    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
) AS dios ON
    dios.object_id = i.object_id AND
    dios.index_id = i.index_id
OUTER APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
        --, CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS oa1
CROSS APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS ca1

WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND 
     o.name NOT LIKE 'sys%' 
    )
ORDER BY
    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
    db_name,
    --i.index_id,
    --ius.user_seeks - ius.user_scans,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    table_name, 
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, 
    key_cols


SELECT *
FROM #index_usage
ORDER BY ident

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL

Open in new window

0
Zoldy2000Author Commented:
Screen shot attached
0
Scott PletcherSenior DBACommented:
There should be two result sets.  You can post them separately as "code" or attach a text file with the results.
0
Zoldy2000Author Commented:
The first result is blank
0
Scott PletcherSenior DBACommented:
Nice, that means SQL didn't detect any "missing indexes", at least since the server/db was cycled.
0
Zoldy2000Author Commented:
Okay great... I am going to increase the memory over the weekend and see what that does if anything .
0
Scott PletcherSenior DBACommented:
The second query listing index usage is critical too.  You can instead throw RAM at it, but if it's a locking/blocking issue with indexes, all the RAM in the world won't help.

Also, schedule the RAM query to run every 30 mins.  If it continues to report "Available memory is high" every time, then it's very unlikely to be a memory issue.
0
Zoldy2000Author Commented:
I increased the RAM and no change.     I really believe it is the application after going through all of this.    Trouble is after hours when no one else is on the system it is almost 3 times as fast.   Which does indicate a load issue.   That is the confusing part.
0
bsarahimCommented:
can u restart the application and see after restart how is the performance of the app
0
Zoldy2000Author Commented:
I am going to award points soon appropriately ... although we did not resolve the issue the suggestions lead to the issue.    We are almost certain it is the application and the developers are getting on board.
0
Zoldy2000Author Commented:
Good troubleshooting techniques and advice provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.