SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database performance issue


Database performance issue

Author
Message
Mh-397891
Mh-397891
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 1514
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.
MarlonRibunal
MarlonRibunal
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 364
Try SQL Server Profiler, and see what the apps are doing.

Marlon Ribunal
http://marlonribunal.com
Twitter @MarlonRibunal
Mh-397891
Mh-397891
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 1514
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.
Mh-397891
Mh-397891
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 1514
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.
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12211 Visits: 8924
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Mh-397891
Mh-397891
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 1514
What value shall I assign to the Minimum server memory?
Is the Maximum server memory value okay?
Thanks.
Mh-397891
Mh-397891
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 1514
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
Nicholas Cain
Nicholas Cain
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3132 Visits: 6200
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
Fidel Rodriguez
Fidel Rodriguez
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12211 Visits: 8924
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search