April 13, 2011 at 12:12 am
I am using window Server 2008 64bit with MS SQL Server 2008 64 bit Service Pack 1 installed. Usually, My DB becomes so slow and respond very poorly. To Solve this problem I restart the SQL Server(MSSQL08) Service. I want permanent solution of this problem.
I would be very thankful for prompt response.
Thanks
Azhar Iqbal
April 13, 2011 at 12:58 am
That is a very big question!
There could be many reasons, but restarting SQL Server is not necessary. My guess would be a combination of poor index choices, or badly written queries.
If you familiarise yourself with SQl Server profiler you will be able to identify the worst-performing queries, Once you've found them if you post the query, plus DDL to descibe the tables in use and if possible the execution plans for them it will become possible to help you fix them.
Mike
April 13, 2011 at 1:01 am
Hi,
It is impossible to help without more (much more) additional information.
I am assuming that your server is SQL dedicated and that there are no other applications running.
I would recommend basic performance monitoring, to start with:
Try the following counters:
-Memory: pages/sec
-Memory: BufferCacheHitRatio
-SQLServer: MemoryManager: TargetServerMemory
-SQLServer: MemoryManager: TotalServerMemory
-Processor: %Processor Time
-PhysicalDisk: Avg Disk Read Queue Length
-PhysicalDisk: Avg Disk Write Queue Length
-SQLServer:Locks :Average wait time (ms)
If possible, try to run a trace. (Remember, Profiler takes resources, so make sure that you are not running it too long and that it has filter. Depending on what your database does, you could use different filters and filtered values could be different, but you could try with Duration>2000 for the beginning).
Also, try to execute queries:
SELECT TOP 10 * FROM SYS.SYSPROCESSES
ORDER BY CPU DESC
SELECT TOP 10 * FROM SYS.SYSPROCESSES
ORDER BY BLOCKED DESC
They could give you the idea of possible CPU demanding queries and possible blocking processes.
Check whether TempDB is rapidly growing.
Check the frequency of data and log files growth.
What kind of connections are your applications using?
Hopefully, someone else will remember something to add to this list, but these are just basics to start with in troubleshooting process.
I would never recommend restarting service without very good reason.
In your case, it just makes problem temporary invisible. Dive into it and I am sure you will resolve it soon. 🙂
April 13, 2011 at 1:12 am
Thanks Dear.
April 13, 2011 at 4:23 am
HopeFully this picture will explain things more.
April 13, 2011 at 4:44 am
CXPACKET waits are down to parralelism. You could try using maxdop 1 to negate that , but IMO its probably a symptom and not the cause.
Please post plans , DDL and statements as per this link :
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2011 at 6:00 am
First thing I'd do is bump the cost threshold for parallelism up to a much higher number. The default of 5 is ridiculously low. Start at 30 and then adjust up or down from there. I'd do that before I just turned off parallelism completely with MAXDOP.
After that, traditional performance troubleshooting. Identify the long running or most frequently called queries using the methods outlined in Gail's articles. Then see what they're doing using the execution plan and make attempts to fix them. Continue for the life of the app.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2011 at 6:06 am
There's no silver bullet when if comes to performance tuning (I wish there was, i have a client in need of one...).
1) Find badly performing procedures
2) Fix badly performing procedures so that they perform better
3) Repeat until system performance is acceptable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 5:14 am
Pl find ziped attached files of Actual execution plan xml file,CPU time file and Schema file of two main tables. I have created some indexes as well given below
CREATE INDEX [IX_CR_CO_LOANEE_LEDGER_RECEIPT_DATE] ON [PRSP_PROD_010909_040411_Rep].[dbo].[CR_CO_LOANEE_LEDGER] ([RECEIPT_DATE])
INCLUDE ([MEMBER_CODE], [LOAN_APPLY_DATE], [RECPA], [RECSC], [RECLSC], [RECPENALTY], [REBATE], [INST_NO])
GO
CREATE INDEX [IX_CR_CO_LOANEE_CHEQUE_DATE] ON [PRSP_PROD_010909_040411_Rep].[dbo].[CR_CO_LOANEE] ([CHEQUE_DATE]) INCLUDE ([LOAN_APPLY_DATE], [MEMBER_CODE], [CO_Code], [CHEQUE_NO], [DISBR_DATE], [SANCTIONED_AMOUNT], [SC], [SANCTION_NO], [BORROWER_NO], [PHASE], [CREDIT_PERIOD], [PAYOFF_DATE], [PURPOSE_CODE], [PRODUCT_CODE], [PACKAGE_CODE], [REPAYMENT_MODE_CODE], [PROJECT_CODE], [DONOR_CODE])
GO
CREATE INDEX [IX_CR_CO_LOANEE_SCHD_DUE_DATE] ON [PRSP_PROD_010909_040411_Rep].[dbo].[CR_CO_LOANEE_SCHD] ([DUE_DATE]) INCLUDE ([MEMBER_CODE], [LOAN_APPLY_DATE], [PA], [SC], [INST_NO])
GO
CREATE INDEX [IX_CR_CO_MEMBER_MEMBER_STATUS] ON [PRSP_PROD_010909_040411_Rep].[dbo].[CR_CO_MEMBER] ([MEMBER_STATUS]) INCLUDE ([CO_CODE])
GO
CREATE INDEX [IX_CR_CO_ACTIVE_Office_Code] ON [PRSP_PROD_010909_040411_Rep].[dbo].[CR_CO] ([ACTIVE], [Office_Code]) INCLUDE ([CO_CODE], [CO_NAME], [CO_TYPE], [STATUS])
GO
Hopefully this will help. I would be very thankful to you for your valuable time.
Regards
Azhar
April 18, 2011 at 5:19 am
What are the queries?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 6:16 am
Dump the results of the table valued functions into temp tables.
The cost associated with the the functions themselves is entirely hidden , it could be 10ms could be 10hrs.
Post the code of the functions for further advice.
April 18, 2011 at 7:14 am
As a side bar, I have an older machine that doesn't have RAR extracters in it, yet (so I can't see the OPs attachment). Someone please tell me if RAR files have some sort of advantage over ZIP files so I stop thinking it's just another toy like when cursors first came out in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2011 at 7:19 am
Higher compression. Ability to split the archive into multiple files (great for huge archives going onto CD/DVD or for getting through email limits). Encryption and passwords. There's far more.
The RAR util can open all sorts of archives, from iso files, rar and zip, tonnes of others.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2011 at 6:34 am
For Further detail, Pl find attached txt file which contains tables, procedure and function definition respectivly. Execution Plan is also attached.
Row counts of tables is as given
1:- Cr_Co_Loanee 536103 rows
2:- Cr_Co_Loanee_Sch 3508922 rows
3:- Cr_CO_Loanee_Ledger 6292867 rows
Pl also consider the following impacts of procedure as well
ProcedureName TimeImpactReadImpactCPUImpact
CR_OverDueDetail_Rpt;1 1200932 110764066808475
Hopefully this will help and sp "CR_OverDueDetail_Rpt" is worstly performing in my DB.
Many Thanks indeed for your help.
Azhar
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply