SQL Server Performance

SQL Server 2008 Express - Very slow SQL execution

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by kfk, Sep 8, 2008.

  1. kfk New Member

    Recently, when I heard SQL Server 2008 had been released I naturally got curious about it, since 2005 was a major improvement over 2000, so I decided to install it.
    After about 3 hours of hell, over 10 reboots, installing several dependencies (which IMO should have been part of the installation, or at least downloaded and installed automatically during it) and figuring out why the "Performance Counter" check was failing even after manually rebuilding the performance counter several times (turns out the setup was checking the wrong registry key, and not the same one the OS was using - who would have thought...) , I finally managed to get it installed.
    Now, I have another problem.
    Executing SQL queries seems to be incredibly slow compared to SQL Server Express 2005, which I still have installed alongside 2008.
    After running the SQL Profiler on both instances, I notice that the same query, run by the exact same program on the 2 instances result in a different event.
    On the 2005 instance, it says "RPC:Completed" with the text "exec sp_executesql N' <sql code goes here>", while on the 2008 one it says "SQL:StmtRecompile" and simply contains the SQL code as its text.
    Basically, when running a very simple SQL ("select * from <table>") on both of the instances, the SQL server 2005 always returns the recordset instantly (00:00:00 on time), while the 2008 instance always takes 2-3 seconds to return the recordset (this is from the same table on both instances, with approximately the same content - 3-4 records less on the 2008 instance actually).
    Does anyone know what could be causing this? Is there any way to fix it?
  2. satya Moderator

    Welcome to the forums.
    What kind of statistics you have collected on this SQL 2008 instance to say it is running slow on CPU or MEMORY or physical disk.
    Have you performed reindex of indexes on the database once it is restore?
  3. kfk New Member

    Thanks for the reply.
    After I ran one of our programs on the instance, it ran through a process simply labeled as "Optimizing database performance". As I didn't write (nor have I seen) the particular piece of code behind this "optimization", I don't know exactly what it did, but I think it at least disabled the AutoClose db option on the databases.
    In any case, after the "optimization", SQLs are now executing at normal speed (instantly) on the 2008 instance as well, and even when installing a new SQL server 2008 instance without "optimizing" the databases, I can't reproduce the slowness anymore, so presumably it has also set some global options in addition to the instance and database specific ones.
    The exact cause remains a mystery, but the problem seems to be solved. [:D]
    As for statistics, I hadn't really checked any statistics, but it seemed logical that since both SQL servers are running on the same computer, at the same time, they should both be able to execute SQLs at the same speed, if not even faster in the new version.
  4. Asken New Member

    I was experiencing the same problem. I had created a 2008 db using 2005 scripts and it was painfully slow even though the number of rows in the largest table was less than 20.
    Rather than looking at the stats created i just ran sp_updatestats on the slow db which fixed the problem. Stats had been created but was outdated. The wierd thing is that options was set to create and update stats automatically. Seems to not have been working at all! Will keep track of it and see if stats are automatically updated from now on.
    IMO this is not resolved but I guess it's a matter for MS to take care of.
  5. satya Moderator

    Welcome to the forums.
    I think this is one of the statistical problem on the newly created database, if the database has been upgraded or scripts are created with backward compatibility the SQL engine will take care of it.
    So what I recommend is drop all the statistics and then recreate them afresh in SQL 2008 in order to optimize the plan guides too.
  6. kfk New Member

    Just thought I'd post an update here, as I just encountered the same issue one someone else's PC.
    This time, after enabling TCP/IP and Named Pipes in Configuration Manager (coincidentally, because I wanted to try connecting to it from my computer on the network) the execution times were reduced to a fraction of what they were before. (before, even running a very basic simple sql would take at least 1 second - afterwards around 0.030s)
    When re-disabling Named Pipes, leaving only "Shared Memory" (enabled by default) and "TCP/IP" active, the execution time once again halved (~0.015s), making it pretty much completely normal.
    So a possible theory is that "Shared Memory", which is the only enabled by default, is either inherently slow, or for some reason just runs slowly on certain computers.

Share This Page