Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jmsewe0
Starting Member
7 Posts |
 Posted - 2012-04-26 : 05:34:46
|
| Ok guys, I am new here, so bear with me :)Took over administering SQL server (.net dev by trade). This sql server is not normalized at all, has way too many indexes, and is just generally a headache for me. Every time we reboot the machine, the performance is extremely slow. And this is not a slow machine (8 procs, 20GB RAM, Fiber Channel RAID etc). I know SQL server loses its cache on a reboot, but this seems a little too slow. We get timeouts, queries for 1000 rows that takes minutes, etc. Once the server is using all the memory allocated to it (about 17GB), it hums along. I assume i can write some kind of script to try and load all the main tables into memory? The performance is so bad right after a reboot and it takes several hours for it to normalize. Any thoughts? |
|
|
jrand77
Starting Member
1 Post |
Posted - 2012-05-02 : 09:41:50
|
| There are a range of potential areas to explore in attempting to resolve this issue, however running a script to load tables into memory is not possible. On restart, SQL Server must first go through a recovery process to bring each database to a consistent point in time. Each database will be brought online after the recovery process against it has completed. This can be a time consuming process. Once a database is online, SQL Server begins responding to submitted queries by analyzing them and creating query plans. Data is loaded into cache as query plans are executed. After a startup this is a very IO intensive process. If the queries are not optimized and the database is not normalized, and the indexes are not tuned, then retrieval from disk can be rather slow (because the query plans can't produce efficient data access methods). If SQL Server is immediately bombarded with hundreds or thousands of queries, you can see why it might take time to work through the "queue". Once the most frequently used query plans have been generated and the bulk of the data associated with those queries has been loaded into cache, SQL Server will incur less overhead in responding to queries and response time improves. In my experience there is no easy fix for this. Start by monitoring SQL Server on startup and beyond to see what is actually happening. During the post-reboot stage, which resources (cpu, IO, Network, etc.) are experiencing the highest utilization? What are the top 10 or 20 most expensive queries? Is there a lot of blocking or deadlocking? Can you control user/application access to the database(s)? Find the most expensive queries and begin optimizing them. Perhaps more importantly, why are you rebooting the server in the first place, and if rebooting is absolutely necessary, is it possible to perform the reboots at a time when the majority of users and applications will not need immediate access? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-05-02 : 11:46:48
|
| Avoid rebooting , seek to complete maintenance without a reboot.All layers of the database server consume a high level of resources upon start up.Applies to : creating execution plans, create database connectionsTry to keep things up and running , avoid restarts. Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Extremely slow performace after reboot