June 22, 2017 at 1:04 am
Hi,
I've recently upgraded from SQL-2005 to SQL-2014. Now I am observing that performance of SQL-2014 is very slow many times(worst than 2005). Even SP_Who2 commands take 25 seconds to execute and many times there are no blocking. I've already reindex all the tables . I've set the compatabilty level of databases to 100 (as it do not accept 90).
Following is the HW details of Server
OS - Windows server 2012/R2
Virual server configuaration
Xeon 3.6 GHZ, 3 CPU with 2Cores
24GB Ram, 800 GB SAN Stoarage
Virtual platform VMWare
Kndly Suggets
June 22, 2017 at 1:32 am
Can you please find Glenn Berry's wait stats queries, capture the waits at one point, wait a couple hours capture them again, take the difference and post the waits with the highest change over the time period?
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
June 22, 2017 at 2:13 am
I would also use SP_WHO_ISACTIVE, to see what is currently using resources.
Always a good starting point.
June 22, 2017 at 4:57 am
Talib123 - Thursday, June 22, 2017 2:13 AMI would also use SP_WHO_ISACTIVE, to see what is currently using resources.
Always a good starting point.
Actually, that's a very old school method. Since 2005 the better approach is to use the dynamic management views such as sys.dm_exec_requests. You can get much better information by combining this and other DMVs.
"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
June 22, 2017 at 4:58 am
Prasanna Kulkarni-265121 - Thursday, June 22, 2017 1:04 AMHi,I've recently upgraded from SQL-2005 to SQL-2014. Now I am observing that performance of SQL-2014 is very slow many times(worst than 2005). Even SP_Who2 commands take 25 seconds to execute and many times there are no blocking. I've already reindex all the tables . I've set the compatabilty level of databases to 100 (as it do not accept 90).
Following is the HW details of Server
OS - Windows server 2012/R2
Virual server configuaration
Xeon 3.6 GHZ, 3 CPU with 2Cores
24GB Ram, 800 GB SAN Stoarage
Virtual platform VMWareKndly Suggets
First, do what Gail says.
Next, keeping the database in compatibility mode means you're not getting some of the benefits of SQL Server 2014. Yes, you're also avoiding potential issues (especially the one caused in some cases by the new cardinality estimation engine). To see if 2014 functionality is going to help, you will have to change the compatibility mode at some point.
"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
June 22, 2017 at 8:13 am
Grant Fritchey - Thursday, June 22, 2017 4:57 AMTalib123 - Thursday, June 22, 2017 2:13 AMI would also use SP_WHO_ISACTIVE, to see what is currently using resources.
Always a good starting point.Actually, that's a very old school method. Since 2005 the better approach is to use the dynamic management views such as sys.dm_exec_requests. You can get much better information by combining this and other DMVs.
sp_WhoIsActive is Adam Machanic's custom query using most of the DMVs.
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
June 22, 2017 at 9:11 am
GilaMonster - Thursday, June 22, 2017 8:13 AMGrant Fritchey - Thursday, June 22, 2017 4:57 AMTalib123 - Thursday, June 22, 2017 2:13 AMI would also use SP_WHO_ISACTIVE, to see what is currently using resources.
Always a good starting point.Actually, that's a very old school method. Since 2005 the better approach is to use the dynamic management views such as sys.dm_exec_requests. You can get much better information by combining this and other DMVs.
sp_WhoIsActive is Adam Machanic's custom query using most of the DMVs.
Crap. Misread it. I only saw sp_who.
Apologies for poor reading skills.
"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
June 23, 2017 at 2:20 am
No worries, You owe me one solution to the next issue I post Grant. Then all is forgiven.
June 23, 2017 at 6:27 am
Talib123 - Friday, June 23, 2017 2:20 AMNo worries, You owe me one solution to the next issue I post Grant. Then all is forgiven.
Ha! Done.
Again, sorry for being a putz.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply