Avatar of TUS11
TUS11
 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!
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steven Kribbe

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Pavel Celba

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott Pletcher

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TUS11

ASKER
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.
TUS11

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?
TUS11

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
TUS11

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_backuphistory '2013-07-23 12:01:55.000 + 3' or

EXEC msdb.dbo.sp_delete_backuphistory '2013-07-23 + 3'
TUS11

ASKER
ok ran this and ran successfully. running this took 21 seconds.

SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset
Pavel Celba

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)?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TUS11

ASKER
resource monitor part in task manager? i am looking at processes tab where it shows memory for each process.
TUS11

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.
TUS11

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
TUS11

ASKER
still expanding - its more than 4 mins.
TUS11

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
Pavel Celba

OK, perfect. Does the Disk still shows 100% ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TUS11

ASKER
i am having a bit of trouble understanding resource monitor.
Pavel Celba

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?
TUS11

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
TUS11

ASKER
above screenshot is when nothing is running on system except sql server instance. no user is accessing computer except me via RDP.
TUS11

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.SQSERVER2012\MSSQL whereas empty space in c drive is 12 GB, can that be an issue with expanding? though it does expand so low memory in that drive cannot be an issue.

hdd-space-screenshot.png
Pavel Celba

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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TUS11

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?
TUS11

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
Pavel Celba

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Pavel Celba

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott Pletcher

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TUS11

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Pavel Celba

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Nakul Vachhrajani

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Gerald Connolly

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Pavel Celba

All contributions are useful.