May 5, 2013 at 2:47 am
We have built a new virtual server with Hyper-V and 64bit windows 2008R2 with 3 processors and 12 GB of ram allocated to the machine. The machine is a dedicated SQL server machine. I have set the max server memory to 10000MB, Use AWE memory is enabled with 255 maximum worker processors.
Initally we had installed a database which is only 3GB in size so pretty small. The database is an uggrade from SQL 2000 to 2008. The problem we are having is that on the new server all the queries are running so much slower to the point that the machine is unusable. An example of this is a simple query that we have (select name from andotype where AndoTypeID = 2) ran in a loop 200 times takes over 8 seconds to execute, the same query on the SQL Server 2000 database on Windows Server 2003 runs in less than one second.
Currently the new database has only one table, with the one row and just a primary key on the AndoTypeID field and it still runs over 8 seconds.
I am not a SQL expert so I really need some help/advice on the right settings to get this to work.
May 5, 2013 at 5:53 am
There are so many things that could be causing this issue. One, there is no Upgrade from one version of SQL to the next. Not sure what you did there. But the newer version can always ready the later version. Have no idea what your index looks like for that table. Did you update the stats once you started using these tables in SQL 2008? You should never have "Loops" in a set based database. SQL Server does not work well when running on a VM. It is good only for something like a Development box. SQL needs to be able to control the hardware and on a VM, it cannot. Could be that the SQL code is out of date, and you could greatly improve performance by going thru the code and making it more efficient. Could be the design of the database. There are so many things that can cause something like this. I would install SQL on actual hardware and run the same query after pumping the data in.
Andrew SQLDBA
May 5, 2013 at 6:33 am
The world is going virtual and even we love to run SQL on Actual hardware, we can not stop it. There are so many factors which is driving virtualization.
I would recommend that you read through it.
http://www.brentozar.com/sql/virtualization-best-practices/
Also try to identify where the issue is.. it is not sql server 2008, that I know. SO look for other issues like resource contention, stale stats etc...
May 5, 2013 at 8:07 am
Start by updating all statistics with full scan. While SQL 2008 can use the older 2000 stats, it's not efficient about it.
If the DB is still slow afterwards, identify the slow queries and tune them and also identify the main waits you're seeing. It literally could be anything since you've upgraded hardware and SQL version, could be IO bottleneck, could be incorrect virtualisation settings, could be inefficient code that blocks like crazy with more processors.
On settings, turn AWE off, it's a meaningless setting on 64 bit and set max worker threads back to default (0), that's a setting that typically shouldn't be changed without a really good reason
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2013 at 8:10 am
AndrewSQLDBA (5/5/2013)
One, there is no Upgrade from one version of SQL to the next. Not sure what you did there. But the newer version can always ready the later version.
Not sure what you mean here because there is an upgrade in SQL Server, you can upgrade one version of SQL to another as part of the installation process.
SQL Server does not work well when running on a VM. It is good only for something like a Development box. SQL needs to be able to control the hardware and on a VM, it cannot.
SQL can work just fine in a VM, production as well as dev, you just have to be sensible about the resource allocations and accept that there's a small overhead from the hypervisor and not overutilise the hardware.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2013 at 8:27 pm
OK so today I did a clean install of SQL still on the same hardware with the same settings, removed the AWE and set the Max worker threads back to 0.
I created a new database with all the defaults and created a new table which has a primary key (incremented), a nvarchar(10) field, and two datetime fields - StartDate and EndDate.
I added one row to the table and created a new query (select Name from testable where ID = 1). This query ran fine, but if I run it 200 times in a row so
select Name from testable where ID = 1
GO
select Name from testable where ID = 1
GO
Etc …
It takes over 6 seconds to execute, whereas with the same table/data/query on SQL2000 it runs in less than 0 seconds. I guess what I need to know is if this is normal behavior for SQL2008 or do I really need to dig deeper?
There is no need to update stats as this is a brand new query, it only has a primary key so all that the execution plans shows is 100% time on the primary key.
I realize that you will never do this in a normal situation but this is just a sample. In the "real world" when it is thrown 200 different queries the same thing happens.
Maybe if someone could replicate the database/table and query and see how long it takes to run, then I can have something to compare it to?
May 6, 2013 at 1:57 am
200 executions of a query taking 6 seconds means that each execution takes 30ms to execute and return results, ignoring the parse and compile time (and SSMS is often slow to display results). Doesn't sound too odd.
If you're trying to identify performance problems, don't use fake setups like that, look at the actual queries that the app executes and test those, preferably. Unless the app really does run a particular query in a loop hundreds of times, testing and tuning that is not going to show you anything useful.
You say all the queries are much slower. Can you find a query that is significantly slower (run once, not in a loop), update the stats on all the tables that it references (you need to do that when upgrading a database from SQL 2000), then if it's still slow afterwards look at the wait stats and execution plan, see what the expensive operators are and what the query is waiting for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2013 at 12:01 pm
kellyascent (5/5/2013)
OK so today I did a clean install of SQL still on the same hardware with the same settings, removed the AWE and set the Max worker threads back to 0.I created a new database with all the defaults and created a new table which has a primary key (incremented), a nvarchar(10) field, and two datetime fields - StartDate and EndDate.
I added one row to the table and created a new query (select Name from testable where ID = 1). This query ran fine, but if I run it 200 times in a row so
select Name from testable where ID = 1
GO
select Name from testable where ID = 1
GO
Etc …
It takes over 6 seconds to execute, whereas with the same table/data/query on SQL2000 it runs in less than 0 seconds. I guess what I need to know is if this is normal behavior for SQL2008 or do I really need to dig deeper?
There is no need to update stats as this is a brand new query, it only has a primary key so all that the execution plans shows is 100% time on the primary key.
I realize that you will never do this in a normal situation but this is just a sample. In the "real world" when it is thrown 200 different queries the same thing happens.
Maybe if someone could replicate the database/table and query and see how long it takes to run, then I can have something to compare it to?
So, you've already confirmed the same query on 2000 and 2008 are both performing a similar index scan on the primary key and number of records, but now you're curious about the significant difference in runtime duration?
Now, re-run the queries again on both servers, but first execute the following, which will insure each starts with a clean page cache and plan cache. Also, for comparison it will return information about time spent compiling the execution plan and number of I/O pages read.
-- Removes all clean buffers from the buffer pool. Use this to test queries with a cold buffer cache without shutting down and restarting the server.
DBCC DROPCLEANBUFFERS;
-- Removes all elements from the plan cache.
DBCC FREEPROCCACHE;
-- Displays the number of milliseconds required to parse, compile, and execute each statement.
SET STATISTICS TIME ON;
-- Display information regarding the amount of disk activity generated by Transact-SQL statements.
SET STATISTICS IO ON;
Also, run sp_spaceused on the table in both 2000 and 2008. Compare the number of pages reserved. It's possible that the table on 2008 may be heavily page fragmented for some reason, perhaps as a result of the migration process. If so, that would result in the query performing more I/O and thus more work.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 7, 2013 at 3:51 am
We run all our production web and SQL servers virtualised and get good performance out of them. You just need to make sure the virtual servers have access to enough resources to get the performance you need.
IO can be a bottleneck with virtual servers, and you should check how good your system is. We use the PAL tool available from Codeplex, and it has helped us find and overcome a number of problems.
It is vital that you run PAL on both your old system and the new one. You will almost certainly find some periods of slow response on the new system, but what you need to find out is how the IO performance compares to what you were getting.
Also, if you have any Windows-level monitoring tools, make sure they are monitoring CPU, memory and IO use on your servers. We found the key IO figures to measure are the perfmon Physical Disk: Disk reads / sec, Physical Disk: Disk writes / sec, Physical Disk: Current disk queue length. This will help you see if you have any serious bottlenecks at the virtual server level.
Beyond your virtual server, find out what the total CPU and memory use is on the physical host. If that is running near 100% CPU you will not get good performance on your virtual server even though you may only see 10% CPU use on the virtual machine.
Finally, you mention you are using SQL 2008, not SQL 2008 R2 or SQL 2012. You will get a small performance penalty running SQL 2008 because it does not support SLAT, which more recent versions of SQL Server do support and which will help performance (about 5%), particularly on a virtual machine.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply