Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database performance issue Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 7:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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
Posted Monday, February 22, 2010 7:46 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:18 PM
Points: 70, Visits: 317
Try SQL Server Profiler, and see what the apps are doing.

Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal
Post #870869
Posted Tuesday, February 23, 2010 8:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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
Posted Tuesday, February 23, 2010 11:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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
Posted Tuesday, February 23, 2010 11:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 28, 2014 12:47 AM
Points: 7,005, Visits: 8,451
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #871360
Posted Tuesday, February 23, 2010 12:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
What value shall I assign to the Minimum server memory?
Is the Maximum server memory value okay?
Thanks.
Post #871374
Posted Tuesday, February 23, 2010 12:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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
Posted Tuesday, February 23, 2010 1:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:29 PM
Points: 2,007, Visits: 6,077
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
Posted Tuesday, February 23, 2010 3:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 5:18 PM
Points: 11, Visits: 192
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
Posted Tuesday, February 23, 2010 11:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 28, 2014 12:47 AM
Points: 7,005, Visits: 8,451
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #871736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse