Database performance issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Database performance issue

17 posts, Page 1 of 2

1 2 »»

Database performance issue

Rate Topic

Display Mode

Topic Options

Author

Message

Mh-397891

Posted Monday, February 22, 2010 7:09 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261, Visits: 1,492

The version of sql server is 2008.
I just restored a backup of a database from the development server to the production server. The database was performing very good in the development server all the seeks were very fast in development server. After I restored the same database onto production server, the data seeks were very slow and our production server has very good hardware much better than development box. When I try to read data from the application even from single table from production environment, it is very slow. I tried every thing like rebuilding indexes etc in the production box, but still
nothing is helping. Trying to figure out how to trouble shoot this performance issue.

Please let me know, how I can find out where the poblem is. Thanks.

Post #870863

MarlonRibunal

Posted Monday, February 22, 2010 7:46 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 68, Visits: 296

Try SQL Server Profiler, and see what the apps are doing.

Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal

Post #870869

Mh-397891

Posted Tuesday, February 23, 2010 8:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261, Visits: 1,492

The application we have is MS Dynamics Navision ERP, the application data seeks are slow in production. Like I said it's fast in Test environment. We ran the trace for some, duration of the command execution is more than 300 ms in prod.

But one thing I do'nt understand is our production environment is far better than test enviornment, the database has same structures and same indexes in both prod and test environments. Now I am trying to figure out how to dump all the configuration/switch information on the servers
as they should be close to identical. Please let me know how I can get the configuration/switch information on the servers so that I can compare from both environments.

Thanks.

Post #871173

Mh-397891

Posted Tuesday, February 23, 2010 11:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261, Visits: 1,492

Here is the configuration of our Production server:
Windows server 2008 R2 Enterprise
4 processors
Installed memory(RAM): 128GB
64 bit OS

Below is the info on the Memory which I got from the sql server properties:
Memory:

Server memory options:
Use AWE to allocate memory: Not checked

Minimum server memory(in MB): 102400
Maximum server memory(in MB): 102400

Given the above information, I am trying to see if there is anything related to the memory issue, where SQL server is not using the available memory.

How do I find out, how much memory SQL Server is using?
I am not sure how to resolve this issue, any help is greatly appreciated. Thanks.

Post #871330

ALZDBA

Posted Tuesday, February 23, 2010 11:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047

These is one big NONO with your configuration:

Minimum server memory(in MB): 102400
Maximum server memory(in MB): 102400

Don't set the same value for min and max server memory ! It confuses sqlserver memory manager !

With 64-bit you don't need to enable AWE because it can directly access all of your ram (+2TB)

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #871360

Mh-397891

Posted Tuesday, February 23, 2010 12:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261, Visits: 1,492

What value shall I assign to the Minimum server memory?
Is the Maximum server memory value okay?
Thanks.

Post #871374

Mh-397891

Posted Tuesday, February 23, 2010 12:24 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261, Visits: 1,492

ALZDBA (2/23/2010)

These is one big NONO with your configuration:

Minimum server memory(in MB): 102400
Maximum server memory(in MB): 102400

Don't set the same value for min and max server memory ! It confuses sqlserver memory manager !

With 64-bit you don't need to enable AWE because it can directly access all of your ram (+2TB)

FYI, Per this article: This says to set both Min and Max memory to the same value:
http://www.sqlmag.com/Articles/ArticleID/37890/pg/2/2.html

Post #871395

Nicholas Cain

Posted Tuesday, February 23, 2010 1:24 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007, Visits: 6,040

Have you compared the execution plans between the two environments to see if they are the same? How about any hits to the disk? How hard is your hardware working processing other queries in production?

Shamless self promotion - read my blog http://sirsql.net

Post #871432

Fidel Rodriguez

Posted Tuesday, February 23, 2010 3:51 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 16, 2012 1:55 PM
Points: 11, Visits: 188

Consider looking at the following:
page life expectancy - (I think 5 mins is the MS best practice) this will tell you how long data pages are living in memory
buffer cache hit ratio - (I think 99+% is the MS best practice) this will tell you how frequently SQL goes to memory for data
pagefile reads - (lower the better) this will tell you if the system is using the page file and reading (memory issue)
disk queue reads / writes - this will tell you if the box is having trouble fetching data from disks assuming it's not in the cache
To me, memory is something that should be configured according to the duties of the box. If, say, the box is solely for SQL, I'd recommend using the majority (90%) for the database engine. Note: this will probably set off alarms (SCOM or other alert software) because it'll look like the box is slammed, but it really isn't. It can also result in crazy-high page life expectancies which isn't a bad thing, but can indicate that the database doesn't need that much memory and can possibly take on more work. I hope that kind of makes sense.
---FR

Post #871532

ALZDBA

Posted Tuesday, February 23, 2010 11:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047

Mh-397891 (2/23/2010)

FYI, Per this article: This says to set both Min and Max memory to the same value:
http://www.sqlmag.com/Articles/ArticleID/37890/pg/2/2.html

The article is dated April 2003. (sql2000)

The NONO is about 64-bit.
Experiences with 64-bit have shown it is better to just have these values deffer.

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #871736

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions