• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

SQL Performance Question

I was recently brought in to evaluate slow performance of an ERP solution. They are running SQL Server 2008 R2. Their database size is about 90GB and the server has 16GB RAM. It was immediately apparent that their server is grossly underpowered and the prime suspect is the drive array. No one knows exactly what drives are in there and how they are configured. Plus one of the lights on the array is blinking red. Given this, we have them ordering a new server that is built to the specs of the ERP solution. My question right now is what can I do with the old server to increase its speed while we wait for the new server? They just rebooted the server on Friday and by Monday morning the tempdb has grown to over 3GB and I cannot spot any process they perform that would require such growth. We will be runninging DBCC CHECKDB and CHECKCATALOG tonight and perhaps REINDEX. What else would be the top tuning procedures for SQL I should look at to get this server to be marginally acceptable? That blinking red light is most concerning but there is nothing I can do about that.
0
rwheeler23
Asked:
rwheeler23
  • 7
  • 4
  • 4
2 Solutions
 
Matt BowlerDB team leadCommented:
Yep, given a flashing red light I'd want to ensure that all databases pass a clean DBCC CHECKDB. Follow this with a full backup.

-Run reindex against the database. If you have a limited window you can prioritise the largest and most fragmented indexes.
-If you rebuild an index then statistics will be updated automatically - if you don't then an update statistics would be a good move.
-Are there any other processes running on the server? What is max server memory set to?
-How many CPU's are there?
-How many datafiles does tempdb have? Where is tempdb located? If we have heavy tempdb use then relocating this database to a separate drive could give a quick performance boost.
0
 
rwheeler23Author Commented:
CHECKDB took over an hour to run and returned no inconsisitencies. This servers runs only SQL with SSRS. It actually has 32 GB and SQL was set to only use 16GB. I changed that to 29GB. The tempdb.mdf was 6GB and the log file for the database was 5GB. I reset the SQL service so the tempdb shrunk down to next to nothing. I also shrunk the log file of the database down to 20MB. Whoever created this server just made one big E drive. It has 14TB of space and only 100GB is being used. The drives appear to be 7500rpm SATA drives. Task Manager reports only 4 CPU's and it is disk I/O that is flat lined all day long. I could try to move tempdb to the C drive as there is just the mdf and one ldf. I will see how it runs today and decide.
0
 
Scott PletcherSenior DBACommented:
>> The tempdb.mdf was 6GB and the log file for the database was 5GB. <<

That could be normal for the processing being done.


>> I reset the SQL service so the tempdb shrunk down to next to nothing. I also shrunk the log file of the database down to 20MB. <<

BAD ideas.  They will just grow again, and less efficiently than if you simply pre-allocated them to the proper size.


Given that you have (literally) terabytes of unused disk space, why are you overly-shrinking tempdb??
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rwheeler23Author Commented:
The databases I work with very rarely expand so to see them of that size was highly irregular. These people are manually entering data. So now I have a starting point where I know the size of the log files. If in a week they have not grown I know something unusual was going on and if I see they grow in a burst I can isolate the day and then investigate what may be causing the expansion. Since no one there will admit to what may be going on I needed to start somewhere.
0
 
Scott PletcherSenior DBACommented:
tempdb is not used for permanent data, but it is used for lots of other things, including processing ORDER BY and GROUP BY clauses.  Therefore, tempdb can grow beyond the size you'd normally expect it to, and even beyond the size of the original data, since many people may all be doing different ORDER BY, groupings, etc., all at the same time.
0
 
rwheeler23Author Commented:
That is true. I was told on Friday that the server had gotten rebooted that night so the tempdb would not have grown to over 6GB on just one day's work. Now if it did, it should have done so again by today and now I have a benchmark to which to compare. This server has the worst performance I have ever seen for this ERP solution. I watched them print an invoice out yesterday and it took 2 minutes. I, and they, will be glad when that new server comes online.
0
 
Scott PletcherSenior DBACommented:
1) Is IFI turned on?

2) Be sure to pre-allocate sufficient log space on all dbs, or you will get significant waits as log files are dynamically extended.
0
 
Matt BowlerDB team leadCommented:
I understand your thinking with tempdb and wanting to isolate times when issues occur. But do so with the knowledge that if tempdb needs to grow it will - and this file allocation takes time and resources. Instant File Initialization will speed up data file growth but log files can't use this mechanism.

The thing with tempdb - if this is indeed the bottleneck - is not so much about space but about the fact that it is on the same drive as the other data and log files. Best practise would be to have it isolated on it's own drive - but I'm not sure that I'd put it on the C drive. That would be asking for trouble if it suddenly grows...

For a server that hosts a 90GB database, 6GB tempdb seems very modest to me.

Good that the DBCCs returned clean.

Have you done any index rebuilds? Defrags? Update statistics? This might be a quick win?

If you want to look at fragmentation levels you could look at:

http://msdn.microsoft.com/en-us/library/ms188917(v=sql.90).aspx

But be aware, this can take a while on large databases, as it reads pages into the buffer pool it can cause some cache churn. Run during a quiet time.
0
 
rwheeler23Author Commented:
Part of what I notice was that the Network Service account was used to start SQL. Once that new server gets here I will have them create a service account and then  under Windows User Rights Assignment assign the “Perform Volume Maintenance Tasks”.
Good point about pre-allocating space, I had forgotten about that one.
0
 
Matt BowlerDB team leadCommented:
That's the one. And if you have Max Server Memory set appropriately (and on the surface your setting seems reasonable) I would also give the service account Lock Pages in Memory
0
 
rwheeler23Author Commented:
The Lock Pages in Memory was the other property to set. I will be checking the growth as soon as they will allow me to get back onto the server. I am reluctant to rebuild anything on this server given its sluggishness. They run 16 hours a day and it may not be finished in time to start the next day. I will ask however about starting it on a Friday night.
0
 
Matt BowlerDB team leadCommented:
If you can't have outages for rebuilding indexes reorganising is a possibility. It's not as effective as a rebuild, but much lighter weight in terms of locks and blocking.

Another option is to just rebuild one or two of the bigger, more fragmented indexes per night during the week. Rather than try an rebuild everything in one window which will take several hours, spread the load across multiple smaller windows.
0
 
Scott PletcherSenior DBACommented:
If it's Enterprise Edition, you can do full rebuilds online.
0
 
rwheeler23Author Commented:
Another good idea. You folks are the best. I will run sys.dm_db_index_physical_stats
to check on the health of the tables and then decide what to do.
0
 
rwheeler23Author Commented:
Great tips, all of them!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now