April 19, 2011 at 12:00 am
Hi Experts,
We have upgraded sql server 2005 enterprise edition to SQL Server enterpise edition R2 in new server with high capacity resources but sql server performance is not coming good.We have already updated the stats and rebuilded all indexes and index partitions.
OLD Server
Total RAM 6 GB .Sql server given 2 GB.
OS windows 2003 std edition 32 bit.
SQL server 2005 ent edition 32 bit.
DB datafiles are lying in CX4 SAN.
New server
Hexacore processor
16GB RAM. SQL Server is given 4 GB.
Windows 2008 R2 std edition 64 bit.
Sql server 2008 ent edition 64 bit.
DB datafiles are lying in CX4 SAN.
What could be the possible reason for this bad sql server performance?
Regards,
Bipan
April 19, 2011 at 2:57 am
Are the execution plans the same ?
Does a stress test tool such as sqliosim , show the same issues ?
April 19, 2011 at 5:25 am
Is the problem the server or certain queries. If it's the server, you need to focus on server settings and configurations. Min/max memory, parallelism, cost thresholds, cpu affinity, all that sort of thing.
Do you have monitoring in place? What are you seeing in the way of wait stats?
"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 19, 2011 at 8:57 am
one more ? how big of a database are you running?
if its server wide like Grant asked then we need some perf numbers
CPU
Page life expectancy
Disk reads
Disk writes
....and so forth
the more info the better
and again like Grant asks we need WAIT numbers......
April 19, 2011 at 9:52 am
Tune the Instance PARAMETER first
===========================
Give Maximum RAM to SQL Server 12 GB
Set Maximum Worker Thread to 574 (Recommanded by Microsoft)
Set Boost SQL Server Priority
Set Network Packet size increase from 4096 to 6144
Set Remote Login Time out 20
Set Remote Queries Time out 300
SAN Partition should be minimum
========================
DATA 1
INDEX 1
LOG 1
MSSQL(SQL Server binaries or installation files)
these are minimum Partition structure in SAN environment
Check bad queries to taking time
========================
SELET st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
order by sp.cpu desc
Check missing indexes required by Optimizer
================================
SELECT * from sys.dm_db_missing_index_details
Every table should have a clustered Index
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 2:08 pm
Did you change the comp level to 100 before updating stats?
Dumb question, but I have made that mistake a few times in the past.
__________________________________________________________________________________
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]
April 19, 2011 at 2:40 pm
Is hyperthreading enabled on the new server?
If so - consider turning it off.
April 19, 2011 at 3:52 pm
Just wondering if you are suffering from power management settings as described in this post:
http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast/
On upgrades to new servers we noticed the clock speed was throttled and the new boxes seemed not so new:-(
Andrew
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply