Solved

SQL Server Management Studio 2008 Too Slow

Posted on 2016-10-25
31
52 Views
Last Modified: 2016-11-21
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!
0
Comment
Question by:TUS11
  • 17
  • 9
  • 2
  • +3
31 Comments
 
LVL 4

Accepted Solution

by:
Steven Kribbe earned 100 total points (awarded by participants)
Comment Utility
things to check:

disk space, maybe the drive is full (I Know)
page file size
check event viewer for any system (error) messages
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 100 total points (awarded by participants)
Comment Utility
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.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points (awarded by participants)
Comment Utility
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_backuphistory '<date_from_above_plus_3_months>'
After that, then:
EXEC msdb.dbo.sp_delete_backuphistory '<date_from_above_plus_6_months>'
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.
0
 

Author Comment

by:TUS11
Comment Utility
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.
0
 

Author Comment

by:TUS11
Comment Utility
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?
0
 

Author Comment

by:TUS11
Comment Utility
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
0
 

Author Comment

by:TUS11
Comment Utility
@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'
0
 

Author Comment

by:TUS11
Comment Utility
ok ran this and ran successfully. running this took 21 seconds.

SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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)?
0
 

Author Comment

by:TUS11
Comment Utility
resource monitor part in task manager? i am looking at processes tab where it shows memory for each process.
0
 

Author Comment

by:TUS11
Comment Utility
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.
0
 

Author Comment

by:TUS11
Comment Utility
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
0
 

Author Comment

by:TUS11
Comment Utility
still expanding - its more than 4 mins.
0
 

Author Comment

by:TUS11
Comment Utility
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
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
OK, perfect. Does the Disk still shows 100% ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:TUS11
Comment Utility
i am having a bit of trouble understanding resource monitor.
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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?
0
 

Author Comment

by:TUS11
Comment Utility
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
0
 

Author Comment

by:TUS11
Comment Utility
above screenshot is when nothing is running on system except sql server instance. no user is accessing computer except me via RDP.
0
 

Author Comment

by:TUS11
Comment Utility
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
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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...
0
 

Author Comment

by:TUS11
Comment Utility
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?
0
 

Author Comment

by:TUS11
Comment Utility
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
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 100 total points (awarded by participants)
Comment Utility
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.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 100 total points (awarded by participants)
Comment Utility
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...
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points (awarded by participants)
Comment Utility
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_backuphistory '20131001'
EXEC msdb.dbo.sp_delete_backuphistory '20140101'
EXEC msdb.dbo.sp_delete_backuphistory '20140401'
EXEC msdb.dbo.sp_delete_backuphistory '20140701'
EXEC msdb.dbo.sp_delete_backuphistory '20141001'
EXEC msdb.dbo.sp_delete_backuphistory '20150101'
EXEC msdb.dbo.sp_delete_backuphistory '20150401'
EXEC msdb.dbo.sp_delete_backuphistory '20150701'
EXEC msdb.dbo.sp_delete_backuphistory '20151001'
EXEC msdb.dbo.sp_delete_backuphistory '20160101'
EXEC msdb.dbo.sp_delete_backuphistory '20160401'
EXEC msdb.dbo.sp_delete_backuphistory '20160701'
EXEC msdb.dbo.sp_delete_backuphistory '20160901'
0
 

Author Comment

by:TUS11
Comment Utility
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.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 100 total points (awarded by participants)
Comment Utility
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.
0
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 100 total points (awarded by participants)
Comment Utility
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).
0
 
LVL 16

Assisted Solution

by:Gerald Connolly
Gerald Connolly earned 100 total points (awarded by participants)
Comment Utility
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
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
All contributions are useful.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now