Performance issue

Question

Hi Friends,

I have faced some Performance issue these days. Two days before one engineer report the SQL Server database is very slow(1/6 of the original speed). The database is loading large data into the cube daily. 

The first thing I looked at id the wait type, I run the qeury below:

    WITH Waits AS 
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, 
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn 
   FROM sys.dm_os_wait_stats 
WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP', 
   'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits 
SELECT W1.wait_type, 
   CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, 
   CAST(W1.pct AS DECIMAL(12, 2)) AS pct, 
   CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 
   FROM Waits AS W1 
INNER JOIN Waits AS W2 
   ON W2.rn <= W1.rn 
   GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct 
   HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold 

The reulst is :

wait_type                                      wait_time_s             pct                   running_pct

------------- -                                   -------------- ----------------- -----------------------

CXPACKET                                         735862.74               15.06                15.06
XE_TIMER_EVENT                              395430.82                8.09                 23.16
REQUEST_FOR_DEADLOCK_SEARCH   394618.43                8.08                 31.24
LOGMGR_QUEUE                               394161.61                8.07                 39.31
XE_DISPATCHER_WAIT                      393060.83                8.05                 47.35
FT_IFTS_SCHEDULER_IDLE_WAIT       390590.40               8.00                  55.35
DISPATCHER_QUEUE_SEMAPHORE      346650.84               7.10                  62.44
CHECKPOINT_QUEUE                          338928.39               6.94                  69.38
ONDEMAND_TASK_QUEUE                   325909.58               6.67                  76.05
BROKER_EVENTHANDLER                     313245.41              6.41                  82.47
BROKER_TO_FLUSH                             197542.61              4.04                  86.51
SOS_SCHEDULER_YIELD                      162977.47              3.34                  89.85
MSQL_XP                                             90629.90               1.86                  91.70
PREEMPTIVE_OS_GETPROCADDRESS      89940.76             1.84                    93.54
SLEEP_BPOOL_FLUSH                            47775.68               0.98                  94.52
LCK_M_SCH_S                                       39641.97            0.81                     95.33

The MAXDOP is set to 4  . I am think of CXPACKET  wait is related with MAXDOP. But not sure whether it is the root cause here .

After running the deallock check query ,no dead lock was found.

select  convert (smallint, req_spid) As spid,  
  rsc_dbid As dbid,  
  rsc_objid As ObjId,  
  rsc_indid As IndId,  
  substring (v.name, 1, 4) As Type,  
  substring (rsc_text, 1, 32) as Resource,  
  substring (u.name, 1, 8) As Mode,  
  substring (x.name, 1, 5) As Status    
 from  master.dbo.syslockinfo,  
  master.dbo.spt_values v,  
  master.dbo.spt_values x,  
  master.dbo.spt_values u    
 where   master.dbo.syslockinfo.rsc_type = v.number  
   and v.type = 'LR'  
   and master.dbo.syslockinfo.req_status = x.number  
   and x.type = 'LS'  
   and master.dbo.syslockinfo.req_mode + 1 = u.number  
   and u.type = 'L'  
   and substring (x.name, 1, 5) = 'WAIT'
 order by spid  

The Max Memory is set to 90GB, the total memory is 128GB. And the resource usage percentage of that time is

60-70% memory, 20-40% CPU.

I also guess it may be a tempdb issue. I ran the query below and get the reulst showed as below:

SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

FileName       FileSizeinMB          (No column name)      GrowthValue          GrowthIncrement

------------ ----------------- --------------------------------- ------------- -----------------------------------

tempdev        1024                     Autogrowth is on.         32768           Growth value is in 8-KB pages.
templog          512                      Autogrowth is on.         32768            Growth value is in 8-KB pages.
tempdev2       768                       Autogrowth is on.         32768            Growth value is in 8-KB pages.
tempdev3       768                       Autogrowth is on.          32768            Growth value is in 8-KB pages.
tempdev4       768                       Autogrowth is on.          32768            Growth value is in 8-KB pages.
tempdev5       768                       Autogrowth is on.           32768            Growth value is in 8-KB pages.
tempdev6       768                        Autogrowth is on.          32768             Growth value is in 8-KB pages.
tempdev7       768                       Autogrowth is on.            32768            Growth value is in 8-KB pages.
tempdev8       768                       Autogrowth is on.            32768            Growth value is in 8-KB pages.

Any suggestion is appreciate.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 1

Without looking at the actual SSIS package its hard to tell where is the problem. But I suggest to do the following:

1) Check the fragmentation of your indexes. You can use the following query to do that:

SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
            SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

Also update the statistics on the warehouse tables using sp_update stats. I can't find issue with your tempdb configuration. I hope this information helps you to resolve the issue.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Peja,

If the data is loading means, i guess inserts happening so take a look on indexes. Generally indexed columns may cause the perfromance hit while inserts are happening. And also please check the fragmentation level. If the fragementation level is high try to redecue it and run ur loads and see how its going.

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 3

I don't see anything wrong with the waits, cxpacket is mostly harmless.

Are you sure that someone didn't just give you an extra 100gb of data, or maybe the distribution suddenly changed and you need to do some general tuning?

As a matter of fact, have you validated that it really does take 6x longer?

Josh

Answer 4

I uploaded the fragement result on the slowly running database in skydrive link.

It looks like the fragement is high. How do you think?

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 5

If the fragmentations is high then that is causing all the problem. I suggest you to rebuild the index based on the fragmentation level. I have written a procedure which you can use to automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%. Code for the procedure can be downloaded from my blog here.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.