1

I know the title thread is a bit ambiguous (and my first post here!) but I am having a very severe production database problem. I am not a DBA, though I know how to work with queries and SQL Manager. The database is constantly under load, as there's a constant input and output. The problems started about a week ago and I've done everything I know to get it working... from Rebuilding Indexes to Updating Statistics to Shrinking DB. As an example of the recent problem I used to run a daily backup at 5PM and it took 20-25 minutes, now its taking over an hour. Some days the thing will work just fine, for example yesterday we didn't have an issue and today its all over the place. Its a 20+GB Database running in a VM with Windwos Server 2008R2, dual Xeon @ 2.4GHZ and 12GB of RAM plus a partition for OS, one for DB and one for backups. As one of the measures I am taking I created a separate Log partition to move the log from the DB HardDisk to its own separate HDD. Haven't yet done it as I need to detach and take everything offline. I checked the fragmentation for the main table (5+ million entries) and it was over 99%... this was AFTER rebuilding the index.

At this point, I am just stuck. The application users use is timing out, transactions not going in or out (financial institution) sometimes. I am constantly monitoring the performance, and heck even the SQL Server Activity Monitor timed out for me a few moments ago. Any suggestions and what else should I do (aside from separating the log and DB) would be greatly appreciated.

EDIT: Server RAM Usage just in case: ram[1]

EDIT2: Code used for rebuilding Index

  ALTER INDEX ALL ON DailyTransactions
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = On);
  • A financial institution doesn't have a DBA to help with this? That sounds pretty scary. – Aaron Bertrand Sep 27 '13 at 15:06
  • We do have a contractor DBA but the "funny" thing is that he's basically as stuck as I am =/ – Lord Relix Sep 27 '13 at 15:16
  • 2
    Well, sorry to be frank, but if I had a plumber who didn't know how to use a pipe wrench, I'd hire a different plumber (and challenge the first guy to stop calling himself a plumber). – Aaron Bertrand Sep 27 '13 at 15:18
  • "Rebuilding the index" on the main table means? Clustered index, Nonclustered index, unique, one index or all indexes on the table? – RLF Sep 27 '13 at 15:30
  • Aaron, I agree with you, but the bigger ups don't want to get someone else right now. RLF, I meant all indexes on the tables, sorry for not being specific. – Lord Relix Sep 27 '13 at 15:37
1

First you need to figure out what the actual problem is. The sys.dm_os_wait_stats DMV can help with that. Most values in there are accumulative, so you need to capture it a few times over an extended period to see what is actually going on.

One of the things that this DMV can tell you is if you have RAM pressure. The graphic you posted is basically useless as SQL Server uses all memory it can get (if it needs to). So this is only showing that that mechanism is working.

Once you figured out what your biggest problem is, come back here to get more help.


There are a few wait_types that can be considered noise. You can filter many of those out with this query:

SELECT wait_type,
       wait_time_ms / 1000.0 AS wait_in_sec,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_sec,
       waiting_tasks_count AS wait_count,
       100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS percentage
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     AND waiting_tasks_count >0
     AND wait_time_ms >0
  ORDER BY wait_time_ms DESC;

(based on http://www.sqlskills.com/blogs/paul/survey-what-is-the-highest-wait-on-your-system/)

  • I know its over an extended amount of time but, just in case the SOS_SCHEDULER_YIELD has a 18515 wait count. CHECKPOINT_QUEUE has a massive wait time with 726847 but only 1 count. Will definitely keep checking it for the day, thanks. – Lord Relix Sep 27 '13 at 15:50
  • i was just running a query from that very same page, specifically sqlskills.com/blogs/paul/… . It already showed me some pretty good data which may indicate an IO issue as the PageIOLatch wait types are both the highest in the list. – Lord Relix Sep 27 '13 at 15:57
  • @Lord Relix - Page IO latch wait times (the time a process within SQL Server is held up waiting for I/O to complete) is indicative of an I/O bound process. This could be a hardware problem or a query optimisation problem where the query optimiser has generated a wildly suboptimal plan for some reason. Take a look in the DMVs for the queries with the most I/O waits. This may give you a clue which queries are causing the problem. – ConcernedOfTunbridgeWells Sep 27 '13 at 20:14
  • @LordRelix - I'd also recommend Mr. Bertrands memory by db and object queries mssqltips.com/sqlservertip/2393/… If the sql server is consuming the max server memory, you could reduce the number of trips to disk (and some of the IO latch) by adjusting the max server memory setting or adding memory. Truthfully, you need a good DBA to review the database, as high IO can be from table scans (missing indexes), non-sargable predicates (bad sql), bad plans (missing indexes, stats out of date), insufficient IOPS, etc. – brian Sep 28 '13 at 15:29
0

very first thing you might need is easing the user's experience with DB. suddenly DB doesn't start performing slow. something has triggered it.

Try to answer following to narrow down your search:

Do you know if any Database or Server level property has been changed recently ? for example, DB Compatibility Level, ANSI settings, MAX/MIN server Memory, MAX DOP, etc..

What is the exact sql server version, execute this "Select @@Version" . see if you have latest SP installed. or if you can find any known issue/bug with specific patch you have installed. this is very less likely but this steps will help you to eliminate options where not to look any further.

Confirm that drive has well enough space where tempdb and this DB is located. what is the current TempDB and Your DB size? MDF and LDF separately. what is the left size on disk where these db are located. check the DB file growth is not restricted to fixed size.

Is this just DB access is slower or the system itself? if whole system is slowing down, do a quick check CPU and memory usage in task manager. Does system itself has enough memory to operate or SQL server has acquired all ?

Is there any other sqlserver instances, Sql Agent, Integration, Analysis or additional services has been recently started ? Confirm that not user/service is active on that and/or depends on that and then stop unnecessary services for troubleshooting time only.

also you mentioned "I checked the fragmentation for the main table (5+ million entries) and it was over 99%... this was AFTER rebuilding the index" Just to confirm what i understand is there only few important table you check and those table has 5M+ of records in it. and After Rebuilding the Indexes on it still has 99% fragmentation? is it only one table has 5M+ and 99% or across few tables ?

0

As you have already experimented a lot like any accidental DBA and which many people are, please try following as a starting point and then we will take this to a next step. This will not make the situation any worst, so don't worry too much and take some more aggressive steps as you are doing already. So:

/* Check if you have sufficent free space available on server*/
GO

EXEC xp_fixeddrives
GO

/*
check current server configuration for these parameters.
*/
GO

SELECT *
FROM sys.configurations
WHERE NAME IN (
        'max degree of parallelism'
        ,'min server memory (MB)'
        ,'max server memory (MB)'
        )
GO

EXEC sp_configure 'show advanced options'
    ,1
GO

RECONFIGURE
GO

EXEC sp_configure 'max degree of parallelism'
    ,'As a starting point, Put initial value as half of total cores to give breathing space to other processes. e.g. 12/2 or 8/2'
GO


GO

EXEC sp_configure 'min server memory (MB)'
    ,6144 -- Production SLQ Server must have atleast 6GB
GO


GO

EXEC sp_configure 'max server memory (MB)'
    ,9216 -- Restrict sql server to max 9Gb to give some memory to other processes
GO

RECONFIGURE
GO

/*
confirm server configuration for these parameters value has been changed.
*/
SELECT *
FROM sys.configurations
WHERE NAME IN (
        'max degree of parallelism'
        ,'min server memory (MB)'
        ,'max server memory (MB)'
        )

Now run this select and check "recovery_model_desc". If it is not nothing (Active Transaction, Log backup etc.) for your database then check Books Online accordingly for resolution:

SELECT sd.NAME
    ,sd.state_desc
    ,sd.is_read_only
    ,sd.user_access_desc
    ,sd.recovery_model_desc
    ,sd.page_verify_option_desc
    ,sd.log_reuse_wait_desc
    ,sd.is_broker_enabled
    ,sd.service_broker_guid
    ,sd.is_published
    ,sd.is_subscribed
    ,sd.is_parameterization_forced
    ,smf.type_desc
    ,smf.NAME
    ,smf.physical_name
    ,smf.state_desc
    ,(CONVERT(DECIMAL(32,2),smf.size) * 8096) AS SizeBytes
    ,(CONVERT(DECIMAL(32,2),smf.size) * 8096) / (1024) AS SizeKB
    ,(CONVERT(DECIMAL(32,2),smf.size) * 8096) / (1024 * 1024) AS SizeMB
    ,(CONVERT(DECIMAL(32,2),smf.size) * 8096) / (1024 * 1024 * 1024) AS SizeGB
FROM sys.master_files smf
INNER JOIN sys.databases sd ON sd.database_id = smf.database_id
WHERE sd.database_id > 4
ORDER BY sd.NAME

After doing this all Now check locks statistics using following and check if top waits are still not SOS_SCHEDULER_YIELD, CXPACKET, CHECKPOINT_QUEUE, PAGEIOLATCH OR LCK_...:

select *
from sys.dm_os_wait_stats
order by max_wait_time_ms desc

If waits are from above list then investigation needs further expension where you need to identify top 10 long running queries.

Are you running reports (Adhoc, SSRS), Log shipping, Mirroring Or replication as well on same server and/or same database?

Please come up with answers or further relevant questions and believe me you can solve this.

Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.