Restoring database to a new faster server but running slow

Hi,
I got a weird problem. I restored a database from a SQL Server which slower
in speed and memory to a new faster server (almost double the speed and
memory). The problem is when I execute a stored procedure in the new server,
it took 20-25 seconds to get the results where as in the old server it took
only 5-6 seconds. Moreover the execution plan also different in both. The
new server has optimised execution plan (it use seeks instead of scans). I
checked the statistics, and updated in the new server. Still it is very slow.
I dont know where could be the problem is.
It'll be a great help if some help to solve this problem.
ThanksAll things being equal, statistics may be the most likely reason for
different execution plans. Run dbcc dbreindex or update statistics to
rebuild.
There are some other information you should provide as well.
- Are both SQL Servers the same build? Run select @.@.version on both and
compare the build number.
- Do you have hyper-threading enabled? If so, set 'max degree of
parallelism' to the number of physical processors (or less).
- Did data and t-log file location change, compared to the old server? Do
new RAID drives have the same I/O characteristics as the drives on the old
server?
Adrian
"Viji" <Viji@.discussions.microsoft.com> wrote in message
news:8D8B9A84-BDDB-40FB-87FB-6756E491BA54@.microsoft.com...
> Hi,
> I got a weird problem. I restored a database from a SQL Server which
> slower
> in speed and memory to a new faster server (almost double the speed and
> memory). The problem is when I execute a stored procedure in the new
> server,
> it took 20-25 seconds to get the results where as in the old server it
> took
> only 5-6 seconds. Moreover the execution plan also different in both.
> The
> new server has optimised execution plan (it use seeks instead of scans). I
> checked the statistics, and updated in the new server. Still it is very
> slow.
> I dont know where could be the problem is.
> It'll be a great help if some help to solve this problem.
> Thanks|||Hi
Thanks for your reply. I checked as you suggested...
a) The new sql server runs SP3 and the old one was SP2
b) Old Server has no hyperthreading and the new server has hyperthreading
enabled. It has two processors and we use all the available processors.
c) Old and New Data and Log file is in same location except the drive
(Earlier it was D: and the later is in E:)
d) We use RAID-5 for both earlier and later
e) The statistics and indexes were updated and upto date
But still have the problem. Is there anyother thing could create the problem?
Thanks for the help.
"Adrian Zajkeskovic" wrote:
> All things being equal, statistics may be the most likely reason for
> different execution plans. Run dbcc dbreindex or update statistics to
> rebuild.
> There are some other information you should provide as well.
> - Are both SQL Servers the same build? Run select @.@.version on both and
> compare the build number.
> - Do you have hyper-threading enabled? If so, set 'max degree of
> parallelism' to the number of physical processors (or less).
> - Did data and t-log file location change, compared to the old server? Do
> new RAID drives have the same I/O characteristics as the drives on the old
> server?
> Adrian
>
> "Viji" <Viji@.discussions.microsoft.com> wrote in message
> news:8D8B9A84-BDDB-40FB-87FB-6756E491BA54@.microsoft.com...
> > Hi,
> >
> > I got a weird problem. I restored a database from a SQL Server which
> > slower
> > in speed and memory to a new faster server (almost double the speed and
> > memory). The problem is when I execute a stored procedure in the new
> > server,
> > it took 20-25 seconds to get the results where as in the old server it
> > took
> > only 5-6 seconds. Moreover the execution plan also different in both.
> > The
> > new server has optimised execution plan (it use seeks instead of scans). I
> > checked the statistics, and updated in the new server. Still it is very
> > slow.
> > I dont know where could be the problem is.
> >
> > It'll be a great help if some help to solve this problem.
> >
> > Thanks
>
>|||Try lowering number of processors used to for a certain query to same number as *physical*
processors in the machine. sp_configure.
Or, try the MAXDOP hint in the query and specify only one or possibly two processors (assuming you
mean that you have two *physical* processors in the machine).
But first, you should check the execution plans on the old and new machine to determine what
differs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Viji" <Viji@.discussions.microsoft.com> wrote in message
news:B128042D-FFE3-4A8C-92FC-6A07F6AAB649@.microsoft.com...
> Hi
> Thanks for your reply. I checked as you suggested...
> a) The new sql server runs SP3 and the old one was SP2
> b) Old Server has no hyperthreading and the new server has hyperthreading
> enabled. It has two processors and we use all the available processors.
> c) Old and New Data and Log file is in same location except the drive
> (Earlier it was D: and the later is in E:)
> d) We use RAID-5 for both earlier and later
> e) The statistics and indexes were updated and upto date
> But still have the problem. Is there anyother thing could create the problem?
> Thanks for the help.
>
> "Adrian Zajkeskovic" wrote:
>> All things being equal, statistics may be the most likely reason for
>> different execution plans. Run dbcc dbreindex or update statistics to
>> rebuild.
>> There are some other information you should provide as well.
>> - Are both SQL Servers the same build? Run select @.@.version on both and
>> compare the build number.
>> - Do you have hyper-threading enabled? If so, set 'max degree of
>> parallelism' to the number of physical processors (or less).
>> - Did data and t-log file location change, compared to the old server? Do
>> new RAID drives have the same I/O characteristics as the drives on the old
>> server?
>> Adrian
>>
>> "Viji" <Viji@.discussions.microsoft.com> wrote in message
>> news:8D8B9A84-BDDB-40FB-87FB-6756E491BA54@.microsoft.com...
>> > Hi,
>> >
>> > I got a weird problem. I restored a database from a SQL Server which
>> > slower
>> > in speed and memory to a new faster server (almost double the speed and
>> > memory). The problem is when I execute a stored procedure in the new
>> > server,
>> > it took 20-25 seconds to get the results where as in the old server it
>> > took
>> > only 5-6 seconds. Moreover the execution plan also different in both.
>> > The
>> > new server has optimised execution plan (it use seeks instead of scans). I
>> > checked the statistics, and updated in the new server. Still it is very
>> > slow.
>> > I dont know where could be the problem is.
>> >
>> > It'll be a great help if some help to solve this problem.
>> >
>> > Thanks
>>|||In addition to what Tibor suggested, you might consider moving your Data
files and Log files to separate physical drives. You can get a performance
boost by separating them physically from one another, and T-Logs shouldn't
be on RAID 5 if it can be avoided (slower writes) - RAID 1 is a better
choice for T-Logs.
Also, not sure exactly what type of operations you're doing, but if you are
performing a lot of write operations, you can incur a performance penalty
every time AutoGrow kicks in.
"Viji" <Viji@.discussions.microsoft.com> wrote in message
news:B128042D-FFE3-4A8C-92FC-6A07F6AAB649@.microsoft.com...
> Hi
> Thanks for your reply. I checked as you suggested...
> a) The new sql server runs SP3 and the old one was SP2
> b) Old Server has no hyperthreading and the new server has hyperthreading
> enabled. It has two processors and we use all the available processors.
> c) Old and New Data and Log file is in same location except the drive
> (Earlier it was D: and the later is in E:)
> d) We use RAID-5 for both earlier and later
> e) The statistics and indexes were updated and upto date
> But still have the problem. Is there anyother thing could create the
> problem?
> Thanks for the help.
>
> "Adrian Zajkeskovic" wrote:
>> All things being equal, statistics may be the most likely reason for
>> different execution plans. Run dbcc dbreindex or update statistics to
>> rebuild.
>> There are some other information you should provide as well.
>> - Are both SQL Servers the same build? Run select @.@.version on both and
>> compare the build number.
>> - Do you have hyper-threading enabled? If so, set 'max degree of
>> parallelism' to the number of physical processors (or less).
>> - Did data and t-log file location change, compared to the old server? Do
>> new RAID drives have the same I/O characteristics as the drives on the
>> old
>> server?
>> Adrian
>>
>> "Viji" <Viji@.discussions.microsoft.com> wrote in message
>> news:8D8B9A84-BDDB-40FB-87FB-6756E491BA54@.microsoft.com...
>> > Hi,
>> >
>> > I got a weird problem. I restored a database from a SQL Server which
>> > slower
>> > in speed and memory to a new faster server (almost double the speed and
>> > memory). The problem is when I execute a stored procedure in the new
>> > server,
>> > it took 20-25 seconds to get the results where as in the old server it
>> > took
>> > only 5-6 seconds. Moreover the execution plan also different in both.
>> > The
>> > new server has optimised execution plan (it use seeks instead of
>> > scans). I
>> > checked the statistics, and updated in the new server. Still it is very
>> > slow.
>> > I dont know where could be the problem is.
>> >
>> > It'll be a great help if some help to solve this problem.
>> >
>> > Thanks
>>|||AFAIK, SP3 introduced changes to SQL optimizer which could affect your
execution plans. Primarily, SP3 has better cost estimation. It is
conceivable that the old plans you were getting on SP2 were incorrect, but
the queries ran faster for you.
Try updating statistics with full scan, run dbcc freeprocache, and then use
Index Tuning Wizard to determine if new indexes improve performance.
Adrian
"Viji" <Viji@.discussions.microsoft.com> wrote in message
news:B128042D-FFE3-4A8C-92FC-6A07F6AAB649@.microsoft.com...
> Hi
> Thanks for your reply. I checked as you suggested...
> a) The new sql server runs SP3 and the old one was SP2
> b) Old Server has no hyperthreading and the new server has hyperthreading
> enabled. It has two processors and we use all the available processors.
> c) Old and New Data and Log file is in same location except the drive
> (Earlier it was D: and the later is in E:)
> d) We use RAID-5 for both earlier and later
> e) The statistics and indexes were updated and upto date
> But still have the problem. Is there anyother thing could create the
> problem?
> Thanks for the help.
>
> "Adrian Zajkeskovic" wrote:
>> All things being equal, statistics may be the most likely reason for
>> different execution plans. Run dbcc dbreindex or update statistics to
>> rebuild.
>> There are some other information you should provide as well.
>> - Are both SQL Servers the same build? Run select @.@.version on both and
>> compare the build number.
>> - Do you have hyper-threading enabled? If so, set 'max degree of
>> parallelism' to the number of physical processors (or less).
>> - Did data and t-log file location change, compared to the old server? Do
>> new RAID drives have the same I/O characteristics as the drives on the
>> old
>> server?
>> Adrian
>>
>> "Viji" <Viji@.discussions.microsoft.com> wrote in message
>> news:8D8B9A84-BDDB-40FB-87FB-6756E491BA54@.microsoft.com...
>> > Hi,
>> >
>> > I got a weird problem. I restored a database from a SQL Server which
>> > slower
>> > in speed and memory to a new faster server (almost double the speed and
>> > memory). The problem is when I execute a stored procedure in the new
>> > server,
>> > it took 20-25 seconds to get the results where as in the old server it
>> > took
>> > only 5-6 seconds. Moreover the execution plan also different in both.
>> > The
>> > new server has optimised execution plan (it use seeks instead of
>> > scans). I
>> > checked the statistics, and updated in the new server. Still it is very
>> > slow.
>> > I dont know where could be the problem is.
>> >
>> > It'll be a great help if some help to solve this problem.
>> >
>> > Thanks
>>