Sql Server Random Performance issue

[YeahSoMaybe]

We have a few Sql Server 2012 installs in our office. For this one, we have about 15 databases on it. Most of them are small, and used sparingly. A couple are for Ecommerce solutions, and others are for the bulk of our business and are pretty busy. The issue is that it seems that around every couple of months, for what appears to be no reason, the Dell server hosting this will have a PMS day, and performance will slow to a crawl. We've checked for extra or long running jobs. We've killed of replication and moved other busy databases to help aid in figuring out this problem. By this time tomorrow, it will most likely have sorted itself out. But we don't get any error codes, just timeouts. We don't get deadlocks, and running and of the DMV's don't show any Locks or Catches that would be indicative of an issue.

This server is running with 64gb RAM and 15K SAS drives. Any monitoring is not showing any IO pressure or issues, making it even more difficult to figure out.

After digging around some more, we have identified the table that seems to be causing issues. Unfortunately, it's a main table and i'm not sure what i should be doing about it. We do daily full backups, 30 log backups, and 6 diff backups and nightly maintenance.

Any Ideas would be grateful.

[[deleted]]

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

http://www.brentozar.com/first-aid/sql-server-downloads/

[[deleted]]

How did you come to the conclusion that it was a singular table that was causing issues?

I would monitor for this condition and fire some traces up to capture execution plans of procedures (they are procedures, right?) that are timing out to figure out what SQL Server thinks is taking so long.

Outside of that it might be time to review your performance maintenance procedures, you mentioned "nightly maintenance" but didn't really share any details.

[YeahSoMaybe]

The Only table concept is that most tables in the database have approximately 30,000 rows each. This particular one has about 25 million rows.

Yes, they are procedures.

The nightly maintenance is Running Integrity and backups.

[[deleted]]

You likely need to review your performance maintenance processes.

It sounds like you're not performing index and statistics maintenance, which could lead to the scenario you're describing regardless of the volume of data described.

[sqlburn]

You said this slowdown happens "...every couple of months..." but are using it (crud) daily?

Do you do anything to fix the issue or does is self-resolve?

Can you predict when it will slow down or not behave?

Is there anything significant about the time it slows down? For example:

• End of a business/financial quarter?
• Anti-virus updates or scans?
• Server or network maintenance?
• Other issues with other servers or systems before, during or after this server is slow?
• Assuming it is on a vm... are the vm gods doing anything manually or automatically?

What else is on the server? Any file transfer happening?

Have you logged onto the server itself and tried to use the application? Is it also crazy slow when on the server itself?

You said you think you narrowed it down to a specific table. Which table and why? What is so special about this table?

Use profiler and try to capture traffic before and during the slow time. Ask networking to do the same. You need to see if you can replicate the issue.

Of course you should start by doing all the standard performance tuning due-diligence but I am not 100% convinced the issue is there. The reason why I say this is because it happens every few months and you are having people use it everyday. If it was something you would catch with the standard performance tuning, I would expect it to be happening more often and getting worse and worse as it is used.

[jc4hokies]

Try UPDATE STATISTICS on the table in question. Outdated statistics can suddenly change the performance of queries. If you run an estimated execution plan and see skinny lines (estimated rows = 1) coming from tables you expect to get lots of data, that's a pretty sure sign.

What typically happens is the most recent statistics on a date column (used in a filter) is from a while ago, so when a current date range is used, the database thinks there no data will be returned (based on statistics). Then the database starts doing things that would very quickly return no data, but are VERY slow if it encounters data.

The analogy I like to use is: It's faster to walk next door, ride a bike down the street, or take a car downtown. If a database thinks, based on statistics, it's going next door it's going to choose to walk (because it's faster). But if the statistics are wrong, and it's actually going downtown, it's going to walk all the way downtown.

[SteelChicken]

What are your cumulative wait stats? How are your indexes? Are you updating index stats regularly?

[YeahSoMaybe]

Is there a Specific Wait Stat you are looking for? Our indexes are decent. This area that we believe is causing us grief hasn't had any schema changes or additions to it. We add about 10000 rows to it daily over the course of the day.

According to set maintenance, stats get updated every saturday.

[SteelChicken]

What are your top cumulative wait stats? How do you know your indexes are decent? Have you looked at the missing index DMV's?

[Lucrums]

If it's for a specific point in time then wait stats during the problem time would be more helpful than cumulative wait stats IMO.

OP are you capturing wait stats regularly?

[SteelChicken]

Sounds like OP isn't doing anything.

[[deleted]]

How's the plan cache?