faster index rebuild from prod server verse local computer.

  • when i run the rebuild index on my local it takes longer than when i run it on the prod server which is a much more powerful server. Not sure what processing would be done locally when i run the rebuild from my local? I've always noticed this however really dont have an explanation

  • I'm not clear on what you're asking.

    Do you mean you have a local copy of the database and it runs index rebuilds slower than the server copy?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • What do you mean when you run the rebuild in local? Do you have a copy of the same database in your developer workstation/ laptop?

    If that is the case it make sense if workstations tend to have smaller configuration in terms of memory/ processor etc...

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • sorry. I wasn’t being very clear. What I mean is when run the index rebuild from my local computer connecting to the prod instance through Management studio the rebuild takes much longer than if I were to remote into the actual prod sql server and run the same rebuild script.

  • it would seem that all of the processing of the index rebuild should occur on the prod sql server box. That must not be the case however.

  • That shouldn't be the case.

    In fact, whether you use SSMS on your machine to connect to the prod instance, or on the production server console to connect to the production instance, the submission is the same. SSMS isn't doing anything. In both cases, you submit the command to the server, across a network link, and the server processes things.

    There must be something else that's different.

  • your local computer does is having the production load if no transaction is happening at your local host for sure it will be very fast since nothing has been updated nor nothing is happening at your local host. so this is expected

  • OK, so you agree, it shouldn't run any faster simply because i'm remoted into the prod server.

    What about querries? Should the same logic apply? It's been my experience that queries take longer to execute from my laptop as opposed to me remoting in and running them directly on the server.

  • Yes, with queries, part of the execution time is transmitting and displaying the result set. With maintenance activities, that's a very much small proportion of the work that's done.

    John

  • Snargables (6/8/2016)


    OK, so you agree, it shouldn't run any faster simply because i'm remoted into the prod server.

    What about querries? Should the same logic apply? It's been my experience that queries take longer to execute from my laptop as opposed to me remoting in and running them directly on the server.

    Try running the queries again after setting

    Query/Query Options/Results/Discard Results after execution

    to get a better comparison with those on-server queries.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Snargables (6/8/2016)


    What about querries? Should the same logic apply? It's been my experience that queries take longer to execute from my laptop as opposed to me remoting in and running them directly on the server.

    They can be different depending on the set options each batch is running under.

    SSMS has default set options and that might be different to the set options on how it is called from, say, the application.

    Also, set options could be different between ssms on server and your machine as you may have changed one of them from the defaults and not the other.

    Also, if you run on your laptop, you could have couple of MB result set, travelling over the network to be displayed on your laptop vs on server no network, so what you may be experiencing is increased latency on your laptop.

    The actual time to complete of the query inside the SQL application on the sqlserver should mostly be the same if you have same set options on 2 different SSMS on server and on remote machine.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply