SQL2008R2 question

cncjay

Well Known Member
Hey All,

We are in the testing phase of using SQLServer 2008R2 SP1 on Windows 2008 x64 platform.

Originally, we were/are on AS/400 for our backend database.

We have migrated our DV (TESTDTA & TESTCTL and Central objects) onto SQL server. We have a custom environment (SQL900) based off the PY900 pathcode where its data and control tables are also on the SQL server whereas the central objects still reside on AS/400. We'll move those along with all the PD databases as well as the system databases during our cutover weekend.

We have started to test to see how the system performance. The initial reaction has not been good. For example, when using P4210 - doing a simple lookup for a particular B/U - it is timing out or taking very long to provide results. Doing basic queries from the query analyzer is taking very long to return results. For example, the F47011, which has around 500,000 records, while the count statement comes back with the result very quickly, doing a select * from crpdta.F47011 is taking three to four minutes. I have also run the EXEC sp_updatestat command but it didn't help.

any tips/direction to start looking into would be fantastic.

regards,
 
Hi,

Start by monitoring network usage (between DB and clients)
and disk activity at the DB server.
 
A simple query of F47011 table (select * from crpdta.f47011), directly from the server's management console takes around 3 minutes to complete, which takes the network out of the equation.
 
Sounds like a database configuration and/or hardware configuration problem.

Can you describe both in some detail?
 
Hardware is: Cisco UCS blade server system - windows 2008 x64 OS running SQL2008 R2 SP1 x64. 180GB RAM dedicated to SQL.

Not sure if there is any hardware/storage issue. Nothing has changed on the networking side. I'm reviewing the activity monitor and don't seem to see anything spiking. It would appear that something within the database not configured correctly.
 
[ QUOTE ]
Hey All,

We are in the testing phase of using SQLServer 2008R2 SP1 on Windows 2008 x64 platform.

Originally, we were/are on AS/400 for our backend database.

We have migrated our DV (TESTDTA & TESTCTL and Central objects) onto SQL server. We have a custom environment (SQL900) based off the PY900 pathcode where its data and control tables are also on the SQL server whereas the central objects still reside on AS/400. We'll move those along with all the PD databases as well as the system databases during our cutover weekend.

We have started to test to see how the system performance. The initial reaction has not been good. For example, when using P4210 - doing a simple lookup for a particular B/U - it is timing out or taking very long to provide results. Doing basic queries from the query analyzer is taking very long to return results. For example, the F47011, which has around 500,000 records, while the count statement comes back with the result very quickly, doing a select * from crpdta.F47011 is taking three to four minutes. I have also run the EXEC sp_updatestat command but it didn't help.

any tips/direction to start looking into would be fantastic.

regards,

[/ QUOTE ]

There are a ton of things one can do to optimize SQL performance on E1. Before you dive into those, check this: http://jeffstevenson.karamazovgroup.com/2011/06/windows-2008s-magic-performance-button.html
 
[ QUOTE ]
Hey All,

We are in the testing phase of using SQLServer 2008R2 SP1 on Windows 2008 x64 platform.

Originally, we were/are on AS/400 for our backend database.

We have migrated our DV (TESTDTA & TESTCTL and Central objects) onto SQL server. We have a custom environment (SQL900) based off the PY900 pathcode where its data and control tables are also on the SQL server whereas the central objects still reside on AS/400. We'll move those along with all the PD databases as well as the system databases during our cutover weekend.

We have started to test to see how the system performance. The initial reaction has not been good. For example, when using P4210 - doing a simple lookup for a particular B/U - it is timing out or taking very long to provide results. Doing basic queries from the query analyzer is taking very long to return results. For example, the F47011, which has around 500,000 records, while the count statement comes back with the result very quickly, doing a select * from crpdta.F47011 is taking three to four minutes. I have also run the EXEC sp_updatestat command but it didn't help.

any tips/direction to start looking into would be fantastic.

regards,

[/ QUOTE ]

Then execute this and report results here:

http://jeffstevenson.karamazovgroup.com/2008/09/determine-index-fragmentation-in-all.html
 
I would recommend doing SQL profiler trace, capturing the query, and showing an execution plan on it. My guess is your indexes and/or stats are whacked. Also if you are running enterprise edition I would highly recommend using page compression on all tables and indexes. We have seen significant performance gains as a result of this.
 
CNCJay,

you say "A simple query of F47011 table (select * from crpdta.f47011), directly from the server's management console takes around 3 minutes to complete, which takes the network out of the equation".

Is that 3 minutes for the results to start scrolling or for all the rows to be displayed (scrolled) on the screen. If the former you have a DBMS problem. If the latter . . . thats just screen delay time.

Also, just to check . . . thats a lot of RAM (180 GIGABYTES) dedicated to SQL Server. How much physical memory is there?
... and is this a VM box?
 
Hi, I hope this help you.

1.- Please describe your storage system. Make sure you don't have storage issues.
2.- If you don't work with internal disks make sure HBAs drivers are properly installed.
3.- Make sure you have SQL Server and Windows logs clean. Running DBCC CHECKDB is recommendable.
4.- Make sure you have right indexes and structures in your database. You can run R9698711 to verify that tables and indexes specifications match their definition with ones in EO.
5.- Run same lookups on a FatClient. Also test with a user with no security settings. This will help you discard Web Server issues.
6.- Monitor disk activity, especially Average Disk Queue Length in Logical disks (if you work with SAN).
http://technet.microsoft.com/en-us/library/cc938959.aspx
7.- Capture sql queries with sql profiler, make sure to capture Cursor activity. Check execution plan to see if your queries are doing table scans or accessing through incorrect indexes. Remember that updatestats by default scans only a sample of data, so run updatestats with full scan or reindex your tables.

When SQL detects serious problems with I/O access, you can find messages in sql server logs. If necessary, work with your storage system.

Also set sql server max server memory parameter to leave available memory for Operating System tasks, next reference could help
http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/
 
Hi,

1: Try to rebuild indexes during off office hours using the scrip in this Microsoft article (See section D)- > http://msdn.microsoft.com/en-us/library/ms188917.aspx

2: Right click on your database server in management studio then navigate to properties and choose memory. Make sure you have set aqeduate minimum and maximum memory. Leave about 2GB for the operating system. For a test database server with about 100 - 150 GB data, you should have anything over 4 GB of physical RAM. 2 GB for the SQL server and 2GB for the OS.

3: Try using the windows event viewer with the SQL filters and see what is causing the spikes .. disk I/O etc ..

Hope this helps..

BR,
Joel
 
also, a true test will be to clear the sql buffer cache between tests. if not, you could have that whole table sitting in cache and not accessing the disks at all.

3m for 500k rows is not good. heavy fragmentation or bad backend disk design sounds like the issue. also, check the tempdb size and pregrow it to a healthy size. if left set out of the box, your performance will be bad each time you restart the service until the database grows out.
 
thank you for all the great feedback, I was away for a few days, just catching up on the recommendations and plan on taking action on the suggestions.
 
Back
Top