Common Server Slowdowns
“My server is slow”A lot of the general server slowness I’ve been seeing recently has been with clients who use SQL daily.
How can you tell it’s SQL slowing the server down?Go into the processes in task manager and look for sqlservr.exe. If it’s taking up a lot of RAM, then it usually is part of the problem. For example, theirs is running at 2gb total between 2 processes. Normally it should be under 500mb but it depends on the system. There are cases when this is not true and they are fully functional with huge databases, but it is something to consider as part of diagnosing a slow server.
Open SQL DatabasesRun MSSMSE (Microsoft SQL Server Management Studio Express) and connect to a database. Sometimes they are listed automatically. Server type should be Database Engine. Server name should be server_name\database_name such as APPSERVER\SBSMONITORING. Authentication should be Windows Authentication. Sometimes you can’t find the right names in the GUI and you can literally type them in and see if they open. You can also go into Services and check for database names in the SQL and MSSQL areas (for example, SQL Server (SQLEXPRESS) would be one). Sometimes you can click the dropbar for Server name and go to Browse and find them that way as well.
Once you connect, expand Databases to see the names. You can ignore anything that says System, master, tempdb, model, or msdb.
Shrinking SQL DatabasesShrinking databases is sometimes a good thing. It acts like defragmenting a hard drive and can help the server get to data quicker and easier. This should be done on large log files or databases if there are complaints about a program being slow.
First, view the log files by clicking on the database name and then clicking the New Query button. Type dbcc sqlperf(logspace) and then press the Execute button. Below the log file sizes are listed in the results tab. Anything over 20mb can be considered large. Take note of the names of the large databases.
If any log files are large, then run the following on it, replacing database_name with each database name:
Right-click on the database name and go to Tasks, Shrink, Database. Click OK and let it execute. It should disappear when it completes. If you get any errors, it might be too large to shrink or busy at the moment. Like defragmenting, sometimes it requires space to shrink so you may have to increase the Initial Size and try again, which will be covered below in Optimizing. You can try again after shrinking the files. Right-click on the database name and go to Tasks, Shrink, Files. Under File Type, choose Data or Log and then click OK to shrink them. Make sure to go back and do BOTH Data AND Log. Do these same steps for each database that is necessary.
Optimizing Databases For SpeedThere are a couple settings that will make databases slower or are bad in combination. Normally these settings can be ignored for small databases (under 50mb) since they don’t make a huge difference in speed for those, but large databases can be very slow because of them.
First, open MSSMSE and open the appropriate connections. Right-click on the database name and go to Properties. Under Options, we want to set Auto Close to False and Auto Shrink to False. Auto Close keeps the database closed between access and can make it very slow when it is constantly opening and closing. Auto Shrink is good for saving space on the hard drive but it can slow down a server if every time it expands the database to make room, this process shrinks it back down and they battle for size.
Next, go to Files. Here you can change the Initial Size (MB) to larger if you are unable to shrink a database down (see above) because the database is 500mb but it says 25mb as initial size. Increase it to over 500mb in that case so it have some room to shrink down. Normally this setting can be ignored. Autogrowth is the focus of this section. I suggest setting the Data File Type to By 100mb, unrestricted growth and the Log File Type to By 10mb, 25mb, 50mb, or 100mb in either restricted or unrestricted. If the log file is very small (< 5mb), then it doesn’t need to increase by 100mb each time, 10mb would be fine.
Corrupt SBS Monitoring DatabaseIn MSSMSE, open SBSMonitoring, expand databases, right click it and go to Properties. If the size is over 2gb then it might be corrupt. Once it reaches 4.5gb it will be impossible to shrink down, and you know for sure it is corrupt.
Every SBS 2008 server has the database SBSMonitoring running, which can get corrupted and grow up to 4gb big. As your database is completely filled up, then you can replace it with a clean empty one, to install your new clean database, please follow these steps:
1. Run services.msc.
2. Stop the Datacollectorsvc service(Windows SBS Manger Service), SQL Server(SBSMONITORING) service (To be able to unlock monitoring database files)
3. Change the name of those 2 files in case you will use them in the future:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring.mdf
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring_log.ldf
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring-bak.mdf
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring_log-bak.ldf
4. Download http://cid-6ca40dd0d4c9caa6.skydrive.live.com/self.aspx/.Public/sbsmonitoring.zip file. (I have these files now if we need them in the future)
5. Unzip the zip file and copy those two files to C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder
Note: you don’t need to do detach or attach database in SQL Management Studio. And that database has already been tuned by the SQL command above.
6. Start the SQL Server(SBSMONITORING) service
7. Start the Datacollectorsvc service(Windows SBS Manger Service)
8/13/2012 05:45:52 pm
It is a note of great interest and information, I appreciate the article you post on your blog.
5/14/2013 12:56:03 am
I like the way blogger presented information regarding the concerned subject. thanks for this blog.
Leave a Reply.
Thecomputerheale.com makes no claims about the efficacy of the information contained in the documents and related graphics published on this website for any purpose. All information, documents and graphics are provided "as is" without any kind of guarantee of effectiveness. Thecomputerhealer.com hereby disclaims all responsibility for the manner in which the information offered on this website is used by you.
In no event shall Thecomputerhealeronline.com be liable for any special, indirect or consequential damages or any damages whatsoever resulting from the loss of use, data or profits arising out of or in connection with the use or performance of information available from this website.
The documents and related graphics published on this website may include technical inaccuracies or typographical errors. Changes are periodically added to the information on this website. Thecomputerhealer.com reserves the right, at its discretion, to change or modify all or any part of this agreement and the content on website at any time, effective immediately upon publication of this notice.
Your continued use of this website constitutes your binding acceptance of these terms and conditions, including any changes or modifications made by Thecomputerhealer.com as permitted above. If, at any time, the terms and conditions of this agreement are no longer acceptable to you, you should immediately cease using this website.