May 12, 2008 at 3:47 am
i am just looking to know that is it good if i restart the SQL services Once in a month to get best sql performances ?
May 12, 2008 at 5:02 am
Restarting the SQL Server Service will not improve server performance. 🙂
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
May 12, 2008 at 5:20 am
but what u think , is it good pratice ?
May 12, 2008 at 6:54 am
Sql Server seldom requires to be restarted. Only if your tempdb grows abnormally and fills the disk space you can restart it to recreate tempdb and free the disk space. So its not a good practice to restart it at regular intervals.
Similar topic was discussed few days back refer, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2853360&SiteID=1
[font="Verdana"]- Deepak[/font]
May 12, 2008 at 8:31 am
I don't think it's a best practice to restart SQL server unless you are having some type of issue with resources, such as the tempdb becoming too large and must be shrunk. SQL Server is good at allocating and reclaiming it's memory so no need to restart to "fix" memory leaks.
As long as you keep your DB tuned, then there should not be any major degradation of service from SQL Server.
Marvin Dillard
Senior Consultant
Claraview Inc
May 12, 2008 at 9:00 am
thanks guys..
acutally i was planing to restart services every month.. acutally tempdb get's bigger and bigger.
so i thought it' good pratice
May 12, 2008 at 9:10 am
In my experience with OLTP SQL Servers restarting is a bad thing from a performance standpoint. You loose you're query plans.
Back in the SQL 2000 days if we rebooted the box on a weekend it could be until Wednesday or Thursday before we could handle all 600 of our branches loggin in again.
We made it a point to NEVER reboot the box if we didn't have to.
But our situation had a ton of data to handle every day for that particular box (and we didn't have budget for a new box until next year).
At any rate. I recommend that you never cycle unless you have to.
Put a hard limit on your Temp db files that you're comfortable with and just keep an eye on it.
@SQLvariant
May 12, 2008 at 9:13 am
tempdb gets reused. If it's getting bigger and bigger either you have open transactions that won't clear (dbcc opentran) or the load is growing.
I never restart services unless we patch things. Our current hoster has restarted with patches, but prior to last September, I applied patches about once a quarter or less and ran our database server easily 3-9 months without restarting.
May 12, 2008 at 9:23 am
Might be an idea to recycle your errorlog every week or so to prevent it from growing very large. But I agree with the others - don't restart SQL Server unless you need to.
John
May 13, 2008 at 7:25 am
I actually recycle my error log on a daily basis. I don't like having to look through it for some obscure entry when I have to weed through all of my backups.
To answer the original question, I believe the advice already posted is sound. I never restart my SQL Servers unless I need to install a patch. The old thinking of having to restart Microsoft OS based machines is really a thing of the past. Server 2003 introduced a level of stability that allows the server to just stay up and running with little intervention (again, outside of patching). I usually run at least six months before I start looking into patches because it just works. Other justifications such as losing query plans and stored procedure cache are just the surface of what you lose when you restart.
I have had times where restarting was required outside of patching but that was due to using an IBM driver to talk to DB2. It had to be run inprocess and was buggy to begin with. Repeated queries to DB2 eventually hosed my SQL Server so I would have to restart it.
May 13, 2008 at 9:38 am
As Steve mentioned, it looks like long transactions are the main culprit here.
It may also be that the SQL code in your instance needs tuning.
Internal objects generated during query processing can consume an immense amount of resources if a query is poorly optimized.
Also make sure you:
- UPDATE STATS regularly (WITH FULLSCAN for large tables)
- RE-INDEX regularly
To find out what queries contribute to large tempdb utilization, run the following at regular intervals during intense activity on the server and store the results to a table for analysis:
SELECT
R1.session_id
, R1.user_objects_alloc_page_count
, R1.user_objects_dealloc_page_count
, R1.internal_objects_alloc_page_count
, R1.internal_objects_dealloc_page_count
, R3.text
,S.[program_name]
,S.login_name
,S.status
,S.cpu_time
,S.memory_usage
,S.total_scheduled_time
,S.total_elapsed_time
,S.last_request_start_time
,S.last_request_end_time
,S.reads
,S.writes
,S.logical_reads
FROM
sys.dm_db_task_space_usage AS R1
INNER JOIN
sys.dm_exec_sessions AS S
ON
R1.session_id = S.session_id
LEFT OUTER JOIN sys.dm_exec_requests AS R2
ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE
R1.session_id > 50
AND
(
R1.user_objects_alloc_page_count > 0
OR R1.user_objects_dealloc_page_count > 0
OR R1.internal_objects_alloc_page_count > 0
OR R1.internal_objects_dealloc_page_count > 0
OR R3.text IS NOT NULL
);
Combine this with the following query to analyze the components with the greatest overall contribution to tempdb size. Run this at the same time as the one above and store also to a table for analysis:
SELECT
SUM(unallocated_extent_page_count)
, SUM(version_store_reserved_page_count)
, SUM(user_object_reserved_page_count)
, SUM(internal_object_reserved_page_count)
, SUM(mixed_extent_page_count)
FROM
sys.dm_db_file_space_usage;
version_store_reserved_page_count: a high value indicates long transactions being the culprit
user_object_reserved_page_count: temp tables, table variables
internal_object_reserved_page_count: internal objects (hash tables, sort tables etc.) created during query execution. In my experience, this has been the major component whenever I see highr tempdb utilization.
See this link for more info: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#EX1AE
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply