asked on
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!
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
Pavel Celba
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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?
ASKER
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.![popup error]()
ASKER
@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
ASKER
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)?
ASKER
resource monitor part in task manager? i am looking at processes tab where it shows memory for each process.
ASKER
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.
ASKER
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
ASKER
still expanding - its more than 4 mins.
ASKER
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.
![resource-monitor-screenshot.png]()
pfa screenshot. by the way it is still expanding. more than 10 mins now. memory as such not increased.
OK, perfect. Does the Disk still shows 100% ?
ASKER
i am having a bit of trouble understanding resource monitor.
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?
ASKER
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.
![cpu tab resource monitor when ssms is CLOSED]()
![mEMORY tab resource monitor when ssms is CLOSED]()
![DISK tab resource monitor when ssms is CLOSED]()
![NETWORK tab resource monitor when ssms is CLOSED]()
ASKER
above screenshot is when nothing is running on system except sql server instance. no user is accessing computer except me via RDP.
ASKER
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
![hdd-space-screenshot.png]()
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...
ASKER
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?
ASKER
here is the screenshot of memory tab when storage and disk activity is expanded and ssms is still closed.![memory-tab-with-storage-and-disk-act.png]()
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
All contributions are useful.
"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.