Sluggish or Slow Database, unsure about maintenance

[shdwdrgn000]

My company has recently installed a new SQL Server on our web server (which is held offsite). It has grown slow when accepting data from our users when they fill out our form on our website.

I though it needed to be compacted and other things. (I am an Access guy and new to SQL Server) and cannot find any type of commands to do these maintenance type jobs.

Can someone please help educate me on how to maintain my new SQL Server. Thank you for all your help in advance.

 

[JohnDTampaBay]

Look for Database Maintenance Plan wizard in BOL on the Index tab, double-click on it then choose the topic of the same name in the Creating and Maintaining Databases location. Most everything the wizard does can also be accomplished in T-SQL, usually with a DBCC command. Check out DBCC statements, overview in BOL. This is just a starting point. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[shdwdrgn000]

Thank you for the start John. I do have a few more questions now. What does BOL stand for? Also what does it mean when you "reorganize data and index pages"? What effect does removing empty space from a database have on server overhead and operation? What is this "shrinking"? Is it like compacting and Access database? Is checking the database interrity and having it do minor repairs a good thing or will it slow down the server more than slightly?

This database is attached to our websites for gathering and holding data, so the overhead and speed of the server is of great importance.

Thank you for you help again.

 

[JohnDTampaBay]

BOL = Books Online. Many of your questions will be answered by reading up on maintenance plans and DBCC. Database maintenance is not a small feat and there is no one correct answer. It will take you a little time to get things tweaked for best performance in your scenario.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLBill]

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

-SQLBill

 

[shdwdrgn000]

Thank you both for your help, I will read up some and come back if I find any more questions. Thank you for pointing me in the right direction.

 

[jgoodman00]

Be very wary of shrinking the db.

When you shrink the db, all of the data is moved into free extents at the start of the file. This has the effect of fragmenting the data, which reduces performance. Therefore, if you shrink the db, make sure you then defragment the indexes. DBCC DBREINDEX is the best tool for this, but it is intensive & requires table locks so will probably cause disruption. DBCC INDEXDEFRAG is less thorough (it doesnt actually recreate the indexes), but it will execute faster & will cause less disruption to users.

It might be worth picking a quiet period to defrag the actual drive(s), then shrink the db, & then run DBREINDEX for all of the indexes.

Also, if it is primarily slow inserting data, look at the indexes you have on the affected tables & check they are absolutely necessary. Indexes reduce insert performance.





James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures