Link to home
Create AccountLog in
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!
ASKER CERTIFIED SOLUTION
Avatar of Steven Kribbe
Steven Kribbe
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of TUS11
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.
Avatar of 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?
Avatar of 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.User generated image
Avatar of 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'
Avatar of TUS11

ASKER

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

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)?
Avatar of TUS11

ASKER

resource monitor part in task manager? i am looking at processes tab where it shows memory for each process.
Avatar of 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.
Avatar of 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
Avatar of TUS11

ASKER

still expanding - its more than 4 mins.
Avatar of 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.

User generated image
OK, perfect. Does the Disk still shows 100% ?
Avatar of TUS11

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?
Avatar of 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.

User generated image

User generated image
User generated image
User generated image
Avatar of TUS11

ASKER

above screenshot is when nothing is running on system except sql server instance. no user is accessing computer except me via RDP.
Avatar of 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.

User generated image
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...
Avatar of 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?
Avatar of TUS11

ASKER

here is the screenshot of memory tab when storage and disk activity is expanded and ssms is still closed.User generated image
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
All contributions are useful.