May 14, 2011 at 5:32 am
Test Environment:
I have MSSQL 08 and MSSQL 08R2 servers ,in our test Environment ,running at two different machines. Same query at MSSQL 08 take 1 second and take 2 minute at MSSQL2008R2. Specification of two machines is given below as.
Machine 1 Running MSSQL 2008 (Window Server 2003 Enterprize 64bit, RAM 4GB, Processor 2.6 GB)
Machine 2 Running MSSQL 2008R2 (Window Server 2003 Enterprise R2 32bit, RAM 3GB, Processor 2.4 GB).
Live Environment:
In our live environment we are facing same sort of problem. We have now MSSQLR20082 on window Server 2003 R2 64 bit with 12 GB RAM. Before we were running MSSQL 08 on window Server 2003 R2 64 with 8GB RAM. But queries are running slow at 2008R2 as compared to MSSQL2008.
Is there any bug in MSSQL 2008R2 or any update or SP is needed to installed.
Please do let me know any query you need to explain my question.
Thanks in Advance.
Azhar:-D
May 15, 2011 at 5:17 am
Hi,
there might be some missing index, incorrect statistics, generally whatever because there are completely separated environments. I would check execution plans of both queries at first. If you can paste them here it would help to identify what might be wrong.
Basically I don't see any reason why those queries wouldn't give same performance results.
Jakub
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
May 15, 2011 at 6:59 am
Install Service Pack 1 for the SQL Server 2008 R2 and CU # 7 is available also
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_Sql_text(sp.sql_handle) st
order by sp.cpu desc
this query provide the bottleneck;
64-Bit system processing is much faster than 32-bit
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 15, 2011 at 7:13 am
Syed Jahanzaib Bin hassan (5/15/2011)
64-Bit system processing is much faster than 32-bit
That is a myth. 64-bit is not about better performance, it's about flat memory addressing up to the maximum allowed by that version/edition of windows.
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
May 16, 2011 at 2:10 am
Thanks JAcyub.
Pl fin attached Execution Plans in XML files of same query at SQL 2008 and sql 2008R2.
Along with this, we also made some changes with the DB when we migrated from 2008 to 2008R2.
1. Shrink Log File to 1MB
2. We dropped one of our table with heavy data and recreate it with same indexes, then insert same data with some modification. All indexes remain same.
I am just mentioning these points for precautions b/c these might be the reason. I have also installed SP1 for Ms SQL Server 2008R2 but situation is same.
Any immediate help would be crucial for us.
Regards
Azhar
May 16, 2011 at 2:29 am
Thanks Dear Jhanzaib,
I have installed SP1 for MSSQL08R2 but situation is same. I have not installed CU#07 b/c I got warning at the Microsoft that it should be installed if you face some particular problem other wise it will creates problems for you.
We also truncated Log file to 1MB, is this can be one of the reason.
Thanks
Azhar
May 16, 2011 at 2:49 am
azhar.iqbal499 (5/16/2011)
1. Shrink Log File to 1MB
Ow, that's a nasty thing to have done. Now every logged operation is likely to cause the log to grow, you'll get excessive VLFs, slow log backups, slow recovery.
I strongly suggest you go and grow the log file back to a reasonable size for the operation of the DB.
Are you sure you recreated the indexes identically? On 2008, the query is doing a seek on the CR_CO_LOANEE_LEDGER table, on 2008 R2 it's doing a scan, indicating that there's no suitable index.
Try creating an index on CR_CO_LOANEE_LEDGER on the columns INST_NO, MEMBER_CODE, LOAN_APPLY_DATE, Due_Date.
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
May 16, 2011 at 6:16 am
Thanks Gail,
How can we bring the log file back to normal size b/c we have no log backup. What is the impact of this on performance of select queries. Before this we truncated log file to 1MB but situation was not bad like this. Now we are totally blocked, our reports are 90% slow.
We take following steps and then situation gone worse
I am mentioning these steps b/c these can be the one of the reason of disastrous and at the same time i am new as DBA.
1: Shrink DB File to 1MB
2: Drop one of our table and reinsert data in it with some modification.
3: Rebuild indexes and set Fill factor of some heavily used(insert/update/delete) tables to (75%)(script is attached).
4: Install SP1 for MSSQL 08R2.
5: Change TempData file at F and Templog file at C.
Your help is needed. I have read also from some blogs that some people face issues while shift from 2008 to 2008R2.
Thanks in advance.
May 16, 2011 at 6:23 am
azhar.iqbal499 (5/16/2011)
Thanks Gail,How can we bring the log file back to normal size b/c we have no log backup.
Database properties and change the file size. If the DB is in simple recovery you need no log backups. It's in full, you must set log backups up.
1: Shrink DB File to 1MB
If you shrunk the data file, you fragmented every single index in the DB. Data files should not be shrunk except under exceptional circumstances. Rebuild every single index you have.
Your help is needed. I have read also from some blogs that some people face issues while shift from 2008 to 2008R2.
There were just about no changes to the database engine between 2008 and 2008R2, there's no logical reason for an overall performance reduction.
Did you check and try what I suggested w.r.t. indexes? It looks like the 2008R2 has different indexes to 2008.
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
May 16, 2011 at 7:17 am
Thanks Gail,
We change the index as you suggested and things are now pretty good and DB is now performing well.
Again many thanks indeed.
Thanks
My another Question is
We have data file of size 7.5 GB then what should be the ideal size of Log file. We don't shrink our Data file, we shrink Log files only. Some time our log file size increased to 50 GB then when we try to shrink Log file size to bring it to default size. we use the following query to do this
ALTER DATABASE PRSP_Prod_010909 SET RECOVERY SIMPLE
DBCC SHRINKFILE(N'PRSP_New_Log', 1)
ALTER DATABASE PRSP_Prod_010909 SET RECOVERY FULL
I am much confused what is acceptable log file size. Should we take log file size backups separately and what is the benefits of this?
If for example , 25GB is ideal size of log file then, then what we should do to maintain it at 25GB if it exceeds this limit for better performance
If we take full back up then is it include Data + log file back? If yes then why should we take log back up separately?
Would you please refer me to some article to clear my concepts about log files, data files and importance of logs.
Thanks for your valuable time and advice as well.
Regards
Azhar
May 16, 2011 at 7:32 am
azhar.iqbal499 (5/16/2011)
My another Question isWe have data file of size 7.5 GB then what should be the ideal size of Log file.
No way to tell. Depends on activity, interval between log backups, replication and other things
We don't shrink our Data file, we shrink Log files only. Some time our log file size increased to 50 GB then when we try to shrink Log file size to bring it to default size. we use the following query to do this
ALTER DATABASE PRSP_Prod_010909 SET RECOVERY SIMPLE
DBCC SHRINKFILE(N'PRSP_New_Log', 1)
ALTER DATABASE PRSP_Prod_010909 SET RECOVERY FULL
Wow, that's serious mismanagement of the log.
Please read through this: http://qa.sqlservercentral.com/articles/64582/
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
May 16, 2011 at 9:24 am
The article that was posted is a must read... and the Log file and it's size mangement seem to be a classic example of a misunderstanding of SQL's inner workings.
but in short...
1. If your DB is regularly and sucessfully backed up.
2. If your Log is regularly and sucessfully backed up.
Then the size of your log file IS at its normal operating size and should not be changed.
This is becase the the backup Jobs will truncate the data inside the log file and stop it from growing.
in this situation the log file will only need to grow beyonfd its size for non-normal operations such large imports or some stucture changes or backup failures. other than that your log file will not grow and stay at its "normal operation" size.
If the log is too large and causing some disk sizing issue you have 2 choices. Add more disks or perform the transaction log backups more often.
Leroy L
May 17, 2011 at 3:38 am
I gone through your Article "Managing Transaction Logs", its provides me such a wonderful information to clear my misconception about logs.
I have still two misconception here
1: Pl tell me If take full backup of Database Regularly then is it necessary to take log backups to control the log file size. Or Should we must take Log backups to control the size of Log file size.
2: We have now Data file size 7 GB and Log file size 2.5 GB, Our 3-4 tables in DB are extensively used and remaining have rare transactions. In Future we are going to implement the Replication to have separate DB for Reports. under this situation what should be ideal Log file size.
Thanks
azhar
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply