In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!
SQL Server Management Studio 2008 Too Slow
Hi
We have installed SQL Server Management studio on a windows 2007 machine. We use this machine as an internal testing server hence host websites on IIS and database on sql server. Management studio is just used for backup purposes and giving rights to new users and all. Though Visual studio is also installed on this machine but it is rarely used whenever there is any bug which we are unable to simulate on local machines.
There are 0-10 people at the maximum accessing the websites installed on this machine. It has been running perfectly fine for last 4 years or so but now we may be approaching nearly about 100 odd databases in sql server and similarly 100 odd websites in IIS.
For the last 1 month, sql server management studio (which I believe is 2008 though I have pasted info below and database instance is 2012) is extremely slow. Now it is that slow that it takes 3-4 minutes to expand the databases and nearly 10-15 mins to show the popup for restoring /backing up any database. This is quite frustrating as we have keep on waiting. It used to be fast earlier.
Here is the configuration of the machine:
Windows 7
Processor Is Core to Duo Intel
RAM – 2GB (initially I used to think it is sql server.exe which is taking lot of memory as it used to reach 1.2 GB which I used to see in the processes tab task manager so I used to restart the sql server.exe service and it used to come down to 100-200 MB’s around and this I am doing as of now as well but why sql server management studio is taking so much of time – that is something I am wondering as it is always around 70-300 MB max). I can increase RAM but this same system has been running fine for last 3-4 years hence wondering it is a software issue than hardware issue.
Microsoft SQL Server Management Studio 11.0.3128.0
Database instance : Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Request Experts Help asap as it is impossible to wait for SSMS for that long!
We have installed SQL Server Management studio on a windows 2007 machine. We use this machine as an internal testing server hence host websites on IIS and database on sql server. Management studio is just used for backup purposes and giving rights to new users and all. Though Visual studio is also installed on this machine but it is rarely used whenever there is any bug which we are unable to simulate on local machines.
There are 0-10 people at the maximum accessing the websites installed on this machine. It has been running perfectly fine for last 4 years or so but now we may be approaching nearly about 100 odd databases in sql server and similarly 100 odd websites in IIS.
For the last 1 month, sql server management studio (which I believe is 2008 though I have pasted info below and database instance is 2012) is extremely slow. Now it is that slow that it takes 3-4 minutes to expand the databases and nearly 10-15 mins to show the popup for restoring /backing up any database. This is quite frustrating as we have keep on waiting. It used to be fast earlier.
Here is the configuration of the machine:
Windows 7
Processor Is Core to Duo Intel
RAM – 2GB (initially I used to think it is sql server.exe which is taking lot of memory as it used to reach 1.2 GB which I used to see in the processes tab task manager so I used to restart the sql server.exe service and it used to come down to 100-200 MB’s around and this I am doing as of now as well but why sql server management studio is taking so much of time – that is something I am wondering as it is always around 70-300 MB max). I can increase RAM but this same system has been running fine for last 3-4 years hence wondering it is a software issue than hardware issue.
Microsoft SQL Server Management Studio 11.0.3128.0
Database instance : Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Request Experts Help asap as it is impossible to wait for SSMS for that long!
Who is Participating?
Your memory seems to be the bottle neck at the first view. 2 GB are not sufficient for the everyday office work but you are talking about 100 databases in SQL Server 2012 Express... Also Windows 7 is NOT server operating system but it is not as problematic part.
Execute the Resource monitor and look what's happening when you execute SSMS. You'll see huge page file usage most probably.
So the critical task is to extend the RAM to 8 GB. BUT the free SQL Express cannot utilize such RAM and you have to upgrade it to the Standard edition at least or use several instances of the SQL Express. Then you'll see. (You may test the speed under the SQL developer edition which is free and then decide about the target edition.)
Also the free MS SQL Express edition does is not optimized to support many concurrent users. The more users the less performance in SQL Express.
Execute the Resource monitor and look what's happening when you execute SSMS. You'll see huge page file usage most probably.
So the critical task is to extend the RAM to 8 GB. BUT the free SQL Express cannot utilize such RAM and you have to upgrade it to the Standard edition at least or use several instances of the SQL Express. Then you'll see. (You may test the speed under the SQL developer edition which is free and then decide about the target edition.)
Also the free MS SQL Express edition does is not optimized to support many concurrent users. The more users the less performance in SQL Express.
Most likely you need to clean out the backup history. SQL keeps internal tables on every db backup and restore.
How to see if this is likely (part of) your problem:
SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset
If that's a long time ago, you very likely need to clear out old data.
EXEC msdb.dbo.sp_delete_backuph
After that, then:
EXEC msdb.dbo.sp_delete_backuph
And so on, up to the most recent date you're willing to lose the backup history for (keep at least a couple of months of history, just in case it's needed for restores).
Further explanation/details:
SQL Server uses msdb -- where the backup/restore history is saved -- to display the dbs in SSMS (don't really know why, it just does). Unless you explicitly delete it, SQL keeps backup history forever.
Best typically is to delete a few months at a time, from oldest date to most recent date.
How to see if this is likely (part of) your problem:
SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset
If that's a long time ago, you very likely need to clear out old data.
EXEC msdb.dbo.sp_delete_backuph
After that, then:
EXEC msdb.dbo.sp_delete_backuph
And so on, up to the most recent date you're willing to lose the backup history for (keep at least a couple of months of history, just in case it's needed for restores).
Further explanation/details:
SQL Server uses msdb -- where the backup/restore history is saved -- to display the dbs in SSMS (don't really know why, it just does). Unless you explicitly delete it, SQL keeps backup history forever.
Best typically is to delete a few months at a time, from oldest date to most recent date.
all seems to be valid answers but i think @ScottPletcher seems to be an issue but i will check after executing. Just an update:
"I have just restarted the computer remotely via mstsc. sql server instance was taking around 700 MB before restarting. now after restarting i am just logged in via mstsc and there is no other user accessing the computer and sql server instance memory usage dropped to 150 MB when it restarted and then i started management studio , then sql server instance went to 320 mb and management studio is at 72 MB and it took 1 minute to expand databases. AND I have tried 2-3 times right clicking on a database>tasks>restore >database but popup is not appearing. i know i will have to leave the computer and it will appear by itself after 10 mins. Any thoughts now?" in all , i can see , there is not more than 500 MB being used in processes tab for all processes and there is still 1.5 GB available in RAM for SSMS but still causing this issue.
"I have just restarted the computer remotely via mstsc. sql server instance was taking around 700 MB before restarting. now after restarting i am just logged in via mstsc and there is no other user accessing the computer and sql server instance memory usage dropped to 150 MB when it restarted and then i started management studio , then sql server instance went to 320 mb and management studio is at 72 MB and it took 1 minute to expand databases. AND I have tried 2-3 times right clicking on a database>tasks>restore >database but popup is not appearing. i know i will have to leave the computer and it will appear by itself after 10 mins. Any thoughts now?" in all , i can see , there is not more than 500 MB being used in processes tab for all processes and there is still 1.5 GB available in RAM for SSMS but still causing this issue.
i am running SSMS from another computer with sa username and password and thought of seeing how much time it takes from another company but other computer expanding database operation already took around 10 minutes to expand?
Why expanding database is taking so much time? Read somewhere that it validates database something from microsoft so should disable that check. let me know if that sounds reasonable?
Why expanding database is taking so much time? Read somewhere that it validates database something from microsoft so should disable that check. let me know if that sounds reasonable?
the restore popup click which i did on one of the databases on the server itself failed . I have attached the screenshot at the end of original post. Dont know how to attach an image in the comment section here as there was no option.
@ScottPletcher - though we store backups in seperate drive. i hope deleting this history will not delete our backup files? though 99% i think answer is no. but still confirming.
backup history date is 2013-07-23 12:01:55.000
so in command i should make the command as :
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
backup history date is 2013-07-23 12:01:55.000
so in command i should make the command as :
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
ok ran this and ran successfully. running this took 21 seconds.
SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset
SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset
Databases expanding in SSMS means a query execution so the slow part is your SQL Express instance. Did you look at Resource monitor in Task manager during the DB list expansion (on the machine where the SQL Server resides)?
resource monitor part in task manager? i am looking at processes tab where it shows memory for each process.
and yes memory didnt increase significantly when i clicked expand all databases. it was 120 MB to start with and then just move to 270 and remained there.
i have expanded databases again after restarting management studio as deleted all backup history till 23-10-2015.
here is screenshot. sql server memory was at 320 before expanding and is not raising significantly and infact came down to 318 now and it is still expanding.
db-expansion-task-manager.png
here is screenshot. sql server memory was at 320 before expanding and is not raising significantly and infact came down to 318 now and it is still expanding.
db-expansion-task-manager.png
ok finally found resource monitor. dont know how to analyze it. but seems like harddisk is faulty as it is showing something 100% and blue line at the top of hdd.
pfa screenshot. by the way it is still expanding. more than 10 mins now. memory as such not increased.
pfa screenshot. by the way it is still expanding. more than 10 mins now. memory as such not increased.
You were talking about 3-4 years without problems. Does it mean the disk was spinning 3-4 years without downtime? It can even happen it does not start up when you switch it off now... What disk type do you use?
yes. disk is running atleast from 2013 onwards. its 500 GB Seagate. attaching all tab screenshots of Resource Monitor now when SSMS is not running. please let me know whether disk is at fault or shall i increase RAM.
above screenshot is when nothing is running on system except sql server instance. no user is accessing computer except me via RDP.
please also find harddisk empty space screenshot.
majority of sql server data files are stored in c drive as of now.
nearly 19 GB sql server data files are stored in C:\Program Files\Microsoft SQL Server\MSSQL11.SQSERVER201
majority of sql server data files are stored in c drive as of now.
nearly 19 GB sql server data files are stored in C:\Program Files\Microsoft SQL Server\MSSQL11.SQSERVER201
It seems the SQL Server is accessing the disk really intensively. If you expand the Storage and Disk Activity window then you'll see more.
If you would have more memory then Windows could use it to buffer your data. The free space on C: does not affect the speed most probably because SQL Server has reserved enough for its own use BUT the Database file fragmentation can affect the performance significantly...
If you would have more memory then Windows could use it to buffer your data. The free space on C: does not affect the speed most probably because SQL Server has reserved enough for its own use BUT the Database file fragmentation can affect the performance significantly...
shall i add 2 GB RAM immediately as of now. will that help a bit and can be tested now itself? tomorrow i can get 8 gb or 16 gb for this machine (will have to check whether the motherboard will support or not). but as of now have 2 gb inside and 1 slot is free so can add another 2 gb.
Thoughts?
Thoughts?
here is the screenshot of memory tab when storage and disk activity is expanded and ssms is still closed.
My recommendation: Buy more memory (to allow data caching in RAM) and a replace the old disk by a new one. Old 500 GB drives are reliable enough and they could work 2 more years but this depends on your understanding of possible risks at this field... BTW, did you think about SSD disk? It would improve the speed significantly.
If these options are not feasible now then you may try to 1. Defragment the G: drive and 2. Copy your databases to G: drive. They'll become defragmented and the access should be much faster then.
If these options are not feasible now then you may try to 1. Defragment the G: drive and 2. Copy your databases to G: drive. They'll become defragmented and the access should be much faster then.
2 more GB RAM is good solution for now. It will allow data caching. The physical DB file fragmentation is also important. Here you may read more: https://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/
BUT remember each upgrade of long time running PC can bring unpredictable problems... Full backup is a must. If you add RAM then be sure to remove the dust from the free RAM slots...
BUT remember each upgrade of long time running PC can bring unpredictable problems... Full backup is a must. If you add RAM then be sure to remove the dust from the free RAM slots...
backup history date is 2013-07-23 12:01:55.000
Yeah, that's way too much history to have in there. Copy and run the commands below, in order:
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
EXEC msdb.dbo.sp_delete_backuph
ok here are my final findings before i leave for the day. Its 3am here.
I have added 2gb more RAM. its 4gb now. but still expanding is taking time and restore problem is still there. i think both of these operations loads complete database files from the disk so it can be harddisk problem or some other problem related to sql server settings so that loading all these databases does not take that much time. i think it is harddisk problem as it used to work 1 month earlier. will try to replicate both steps told by @pcelba tomorrow. a new harddisk as well defragmation.
Also never knew Motherboard main chip which is like a small tower where Gigabyte is written in G-41 motherboard can be quite hot. It was quite hot in previous PC so i thought , it can be motherboard problem hence computing might be taking long hence i took the harddisk from this computer and attached to exactly similar configiuration another computer with same motherboard and everything. a bit faster though but that chip in the other computer also got hot in like 15 minutes whereas this computer was not running and the former has been running all these days except occasional restart which we are doing from time to time. so transferred hdd back to old computer. IS THIS NORMAL HOTNESS OF MAIN MOTHERBOARD CHIP? It was quite hot. i could not keep my finger for more than 10 seconds.
@ScottPletcher - already deleted history till 23-oct-15 so dont think it is the real problem.
I have added 2gb more RAM. its 4gb now. but still expanding is taking time and restore problem is still there. i think both of these operations loads complete database files from the disk so it can be harddisk problem or some other problem related to sql server settings so that loading all these databases does not take that much time. i think it is harddisk problem as it used to work 1 month earlier. will try to replicate both steps told by @pcelba tomorrow. a new harddisk as well defragmation.
Also never knew Motherboard main chip which is like a small tower where Gigabyte is written in G-41 motherboard can be quite hot. It was quite hot in previous PC so i thought , it can be motherboard problem hence computing might be taking long hence i took the harddisk from this computer and attached to exactly similar configiuration another computer with same motherboard and everything. a bit faster though but that chip in the other computer also got hot in like 15 minutes whereas this computer was not running and the former has been running all these days except occasional restart which we are doing from time to time. so transferred hdd back to old computer. IS THIS NORMAL HOTNESS OF MAIN MOTHERBOARD CHIP? It was quite hot. i could not keep my finger for more than 10 seconds.
@ScottPletcher - already deleted history till 23-oct-15 so dont think it is the real problem.
Yes, the small tower is the passive cooler on the Intel G-41 chipset probably and it can be hot.
Databases expanding should not load complete databases. It should read some system info like when you execute
SELECT * FROM sys.databases
in SSMS.
Now when you have more gigs of RAM the second database expansion should take reasonable lower time than the first one because the memory can be used for caching.
Another option you could think about is to split your databases between two or more SQL Express instances. Do you use all your databases proportionally? Do you use all of them? If you have just one or a few DBs used more frequently then you could dedicate one SQL instance for them and open the rest when needed only.
Databases expanding should not load complete databases. It should read some system info like when you execute
SELECT * FROM sys.databases
in SSMS.
Now when you have more gigs of RAM the second database expansion should take reasonable lower time than the first one because the memory can be used for caching.
Another option you could think about is to split your databases between two or more SQL Express instances. Do you use all your databases proportionally? Do you use all of them? If you have just one or a few DBs used more frequently then you could dedicate one SQL instance for them and open the rest when needed only.
Do you have any add-ins or 3rd party tools deployed that show up on SSMS?
Every add-in (be it Microsoft or 3rd party) will consume some memory. Also, every window that you have open (Object Explorer, Query Window, Object Explorer Details, etc) will consume memory separately and require separate connections to your SQL Server instance.
Try keeping only required add-ins and windows open in SSMS. Also, try to see if your add-ins can be activated on a requirement basis (for example, some Add-ins always try to update their own Intellisense-type data whenever you connect and whenever you open the query window - this will connsume both time and memory).
Every add-in (be it Microsoft or 3rd party) will consume some memory. Also, every window that you have open (Object Explorer, Query Window, Object Explorer Details, etc) will consume memory separately and require separate connections to your SQL Server instance.
Try keeping only required add-ins and windows open in SSMS. Also, try to see if your add-ins can be activated on a requirement basis (for example, some Add-ins always try to update their own Intellisense-type data whenever you connect and whenever you open the query window - this will connsume both time and memory).
Adding RAM to windows is always a good idea, 4GB is not really enough, 8GB would be better.
I dont think there is anything wrong with your disk, you are just asking it to do too much at once.
AS you probably know there are Two important metrics for a disk, Throughput and Bandwidth,
Throughput is expressed in MB/sec and shown in Green on the graph, whereas Bandwidth is normally expressed as IOPS and is shown in Blue on the graph as a percentage of the maximum the disk can do.
If the disk useage is 100% (then the Blue line will be at the top of the graph) then you have consumed all the IOPS the disk can give (this is typically between 100 &150 IOPS per spindle).
The quickest way to resolve this is to replace the Harddrive with a SSD that will do about 2-3*Throughput of your existing spinning rust, but 100*Bandwidth
I dont think there is anything wrong with your disk, you are just asking it to do too much at once.
AS you probably know there are Two important metrics for a disk, Throughput and Bandwidth,
Throughput is expressed in MB/sec and shown in Green on the graph, whereas Bandwidth is normally expressed as IOPS and is shown in Blue on the graph as a percentage of the maximum the disk can do.
If the disk useage is 100% (then the Blue line will be at the top of the graph) then you have consumed all the IOPS the disk can give (this is typically between 100 &150 IOPS per spindle).
The quickest way to resolve this is to replace the Harddrive with a SSD that will do about 2-3*Throughput of your existing spinning rust, but 100*Bandwidth
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.
All Courses
From novice to tech pro — start learning today.
disk space, maybe the drive is full (I Know)
page file size
check event viewer for any system (error) messages