Performance

Hi,
Our production server is running very slow. A query on Staging server takes
less than 1 minute is taking more than 8 minutes on Production. It's the same
with all the databases on Production server. I have looked and found that
there is no blocking. What's the best way to troubleshoot ? Thanks.Start with comparing the execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server takes
> less than 1 minute is taking more than 8 minutes on Production. It's the same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.|||The best way is to get an expert in to give you a quick performance review,
and then mentor you on how to be better at it yourself. You could waste
days or weeks checking this and that as recommended by forum posters but a
consultant could nail it down in a few hours or even minutes.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server
> takes
> less than 1 minute is taking more than 8 minutes on Production. It's the
> same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.|||I did just that and the execution plans are exactly same. The jobs for
optimizing and defargging the indexes are fine and running as scheduled. The
only difference between the two servers is that the Staging is on the Local
Network and the Production is at a different location. Could it be the
network pipe causing the delay?
"Tibor Karaszi" wrote:
> Start with comparing the execution plans.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> > Hi,
> >
> > Our production server is running very slow. A query on Staging server takes
> > less than 1 minute is taking more than 8 minutes on Production. It's the same
> > with all the databases on Production server. I have looked and found that
> > there is no blocking. What's the best way to troubleshoot ? Thanks.
>|||It could. It depends on your query. If your query returns a large
dataset, or if your query isn't actually a query, but a batch with a
loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
of your batch.
Gert-Jan
sharman wrote:
> I did just that and the execution plans are exactly same. The jobs for
> optimizing and defargging the indexes are fine and running as scheduled. The
> only difference between the two servers is that the Staging is on the Local
> Network and the Production is at a different location. Could it be the
> network pipe causing the delay?
> "Tibor Karaszi" wrote:
> > Start with comparing the execution plans.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> > > Hi,
> > >
> > > Our production server is running very slow. A query on Staging server takes
> > > less than 1 minute is taking more than 8 minutes on Production. It's the same
> > > with all the databases on Production server. I have looked and found that
> > > there is no blocking. What's the best way to troubleshoot ? Thanks.
> >
> >|||You are right, it is returning a large data set (> 600,00 rows). The
difference is just a second for queries returning 5000 rows.
"Gert-Jan Strik" wrote:
> It could. It depends on your query. If your query returns a large
> dataset, or if your query isn't actually a query, but a batch with a
> loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
> of your batch.
> Gert-Jan
>
> sharman wrote:
> >
> > I did just that and the execution plans are exactly same. The jobs for
> > optimizing and defargging the indexes are fine and running as scheduled. The
> > only difference between the two servers is that the Staging is on the Local
> > Network and the Production is at a different location. Could it be the
> > network pipe causing the delay?
> >
> > "Tibor Karaszi" wrote:
> >
> > > Start with comparing the execution plans.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://sqlblog.com/blogs/tibor_karaszi
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> > > > Hi,
> > > >
> > > > Our production server is running very slow. A query on Staging server takes
> > > > less than 1 minute is taking more than 8 minutes on Production. It's the same
> > > > with all the databases on Production server. I have looked and found that
> > > > there is no blocking. What's the best way to troubleshoot ? Thanks.
> > >
> > >
>|||I've seen cases where AV programs will AV the named pipes used between the server and the client.
So, if the client connects using Named Pipes, you want to check this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:68E0A5EA-9B90-4A24-A0BF-754E5A96B530@.microsoft.com...
> You are right, it is returning a large data set (> 600,00 rows). The
> difference is just a second for queries returning 5000 rows.
> "Gert-Jan Strik" wrote:
>> It could. It depends on your query. If your query returns a large
>> dataset, or if your query isn't actually a query, but a batch with a
>> loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
>> of your batch.
>> Gert-Jan
>>
>> sharman wrote:
>> >
>> > I did just that and the execution plans are exactly same. The jobs for
>> > optimizing and defargging the indexes are fine and running as scheduled. The
>> > only difference between the two servers is that the Staging is on the Local
>> > Network and the Production is at a different location. Could it be the
>> > network pipe causing the delay?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > Start with comparing the execution plans.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://sqlblog.com/blogs/tibor_karaszi
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
>> > > > Hi,
>> > > >
>> > > > Our production server is running very slow. A query on Staging server takes
>> > > > less than 1 minute is taking more than 8 minutes on Production. It's the same
>> > > > with all the databases on Production server. I have looked and found that
>> > > > there is no blocking. What's the best way to troubleshoot ? Thanks.
>> > >
>> > >