SQL Server Performance Local vs Remote.
in [Programming SQL]
|
From: Jon Spivey on 6 May 2010 11:57 I've got an asp.net script running on a Windows Web Server 2008 R2 dedicated box. The script reads a .csv file line by line processes the data and sticks it into SQL Server. When connecting to a remote sql server 2008 on a shared web host the script takes about 20 minutes to run, which is perfectly acceptable given the amount of data. When connecting to a local instance of SQL Server 2008 Enterprise the same script takes around 4 hours - obviously not acceptable. Is there some reason a script would run so much slower connecting to a local SQL Server - if anything shouldn't it be faster? Or any hints on establishing why it's running so slow? Cheers, Jon
From: Andrew J. Kelly on 6 May 2010 15:54 There are lots of things it can be but what is the difference in the hardware between the 2 box's? Look to see what it's waiting on. What do the wait stats tell you? Is it blocking? -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message news:#9WGcTT7KHA.5464(a)TK2MSFTNGP05.phx.gbl... > I've got an asp.net script running on a Windows Web Server 2008 R2 > dedicated box. The script reads a .csv file line by line processes the > data and sticks it into SQL Server. When connecting to a remote sql server > 2008 on a shared web host the script takes about 20 minutes to run, which > is perfectly acceptable given the amount of data. When connecting to a > local instance of SQL Server 2008 Enterprise the same script takes around > 4 hours - obviously not acceptable. > > Is there some reason a script would run so much slower connecting to a > local SQL Server - if anything shouldn't it be faster? Or any hints on > establishing why it's running so slow? > > Cheers, > Jon > > >
From: Jon Spivey on 6 May 2010 16:13 I know the hardware on my own box but no idea - and no way to find - the hardware on the remote box. How would I find wait stats? Thanks, Jon "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:OynrqXV7KHA.2292(a)TK2MSFTNGP04.phx.gbl... > There are lots of things it can be but what is the difference in the > hardware between the 2 box's? Look to see what it's waiting on. What do > the wait stats tell you? Is it blocking? > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message > news:#9WGcTT7KHA.5464(a)TK2MSFTNGP05.phx.gbl... >> I've got an asp.net script running on a Windows Web Server 2008 R2 >> dedicated box. The script reads a .csv file line by line processes the >> data and sticks it into SQL Server. When connecting to a remote sql >> server 2008 on a shared web host the script takes about 20 minutes to >> run, which is perfectly acceptable given the amount of data. When >> connecting to a local instance of SQL Server 2008 Enterprise the same >> script takes around 4 hours - obviously not acceptable. >> >> Is there some reason a script would run so much slower connecting to a >> local SQL Server - if anything shouldn't it be faster? Or any hints on >> establishing why it's running so slow? >> >> Cheers, >> Jon >> >> >>
From: Andrew J. Kelly on 6 May 2010 21:30 Have a look at these: http://technet.microsoft.com/en-us/library/cc966413.aspx http://technet.microsoft.com/en-us/library/cc966540.aspx -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message news:uIBYciV7KHA.5476(a)TK2MSFTNGP06.phx.gbl... > I know the hardware on my own box but no idea - and no way to find - the > hardware on the remote box. How would I find wait stats? > > Thanks, > Jon > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:OynrqXV7KHA.2292(a)TK2MSFTNGP04.phx.gbl... >> There are lots of things it can be but what is the difference in the >> hardware between the 2 box's? Look to see what it's waiting on. What do >> the wait stats tell you? Is it blocking? >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message >> news:#9WGcTT7KHA.5464(a)TK2MSFTNGP05.phx.gbl... >>> I've got an asp.net script running on a Windows Web Server 2008 R2 >>> dedicated box. The script reads a .csv file line by line processes the >>> data and sticks it into SQL Server. When connecting to a remote sql >>> server 2008 on a shared web host the script takes about 20 minutes to >>> run, which is perfectly acceptable given the amount of data. When >>> connecting to a local instance of SQL Server 2008 Enterprise the same >>> script takes around 4 hours - obviously not acceptable. >>> >>> Is there some reason a script would run so much slower connecting to a >>> local SQL Server - if anything shouldn't it be faster? Or any hints on >>> establishing why it's running so slow? >>> >>> Cheers, >>> Jon >>> >>> >>> > >
From: TheSQLGuru on 8 May 2010 10:13
>> How would I find wait stats? Go to PASS and attend Andy's incredibly good presentation on the topic!! :) -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message news:uIBYciV7KHA.5476(a)TK2MSFTNGP06.phx.gbl... >I know the hardware on my own box but no idea - and no way to find - the >hardware on the remote box. How would I find wait stats? > > Thanks, > Jon > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:OynrqXV7KHA.2292(a)TK2MSFTNGP04.phx.gbl... >> There are lots of things it can be but what is the difference in the >> hardware between the 2 box's? Look to see what it's waiting on. What do >> the wait stats tell you? Is it blocking? >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message >> news:#9WGcTT7KHA.5464(a)TK2MSFTNGP05.phx.gbl... >>> I've got an asp.net script running on a Windows Web Server 2008 R2 >>> dedicated box. The script reads a .csv file line by line processes the >>> data and sticks it into SQL Server. When connecting to a remote sql >>> server 2008 on a shared web host the script takes about 20 minutes to >>> run, which is perfectly acceptable given the amount of data. When >>> connecting to a local instance of SQL Server 2008 Enterprise the same >>> script takes around 4 hours - obviously not acceptable. >>> >>> Is there some reason a script would run so much slower connecting to a >>> local SQL Server - if anything shouldn't it be faster? Or any hints on >>> establishing why it's running so slow? >>> >>> Cheers, >>> Jon >>> >>> >>> > > |