July 16, 2009 at 4:12 pm
Please review my servers configuration and let me know what steps you suggest. I always get complaints for slow running queries.
Physical Memory20 GB
Virtual Memory39.6 GB
CPU's8
CPU Speed3000 MHz
CPU TypePIII Xeon
OS Win 2003 Enterprise(SP2)
SQL Server 2005-Enterprise(SP2)
Avg DBSize 1.5 TB
No PK's on any tables but we do have clustered/non clustered indexs on all the tables
I am trying to do some analysis so that i can decide on if i need to add any resource to my existing servers but not sure where to start, could some one point me to right direction.
If i run some complicate queries and records time,CPU/memory utilization wud that help?
July 16, 2009 at 9:55 pm
64 or 32? Guessing 64..
Hardware looks pretty good. How are your drives laid out? Data on one volume? Logs on another? How many drives? Local or SAN or ???? RAID configuration of those drives. How often do you re-index? Have you traced any long running queries to see what they are doing EXACTLY and what objects they are utilizing? Have you reviewed the query plans of the slow running queries to see where they are spending their time?
You could have one or two processes that are HEAVILY used but REALLY inefficient, you need to find out. You also like have some other processes that are rarely used, ignore them for now, we are looking at bang-for-the-buck. An inefficient heavily used process is more expensive that a rarely used inefficient process.
CEWII
July 17, 2009 at 12:55 am
Why no primary keys?
With performance problems, the first place to look is the queries and indexes, not the hardware. These two articles may help you get started.
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
July 17, 2009 at 6:58 am
There are no PK;s since begining but they do have clustered index I dont think if that serves as PK , Actualy we do not have a unique key on our data may be we can do a composite key but we may not change the structure at this point.
All our drives are on SAN and ofcourse data and log files are on different drives and more over all over data is in monthly partition.
I did check query plans many times for long runing queries but did not find any weired stuff there and there were no index scans at all.
Most of our production data is used just to read and we do not update any data directly on production. I wonder if 15 concurrent users trying to read only 1 database does they lock themselves or wait for 1 process to complete for another processs to start.
Here is one of the sample queries
drop table #emp
select distinct EMPID
into #emp
from vwEmp_act
where (place IN ('21','25','51') or (empcd = '1' and jobcode = 'Y'))
AND empflag = 'F' and (servcdate between '1/1/2005' and '1/1/2009')
go
select distinct a.empid
into #step1_ct
from dbo.vwEmp as a inner join RevEmp.dbo.EmploerRaw as b
on a.empspec = b.speccode, #DateCriteria
where servcdate between serv_start and serv_end
and
(a.regid=b.regid)
and
(b.RegEmpSpec in (7,12))
July 17, 2009 at 7:07 am
Tara (7/17/2009)
There are no PK;s since begining but they do have clustered index I dont think if that serves as PK , Actualy we do not have a unique key on our data may be we can do a composite key but we may not change the structure at this point.
No pk and no unique index? Why not? How do you prevent duplicate rows?
I did check query plans many times for long runing queries but did not find any weired stuff there and there were no index scans at all.
So what are people complaining is slow? If you don't have long running queries, what are the complaining about?
Most of our production data is used just to read and we do not update any data directly on production. I wonder if 15 concurrent users trying to read only 1 database does they lock themselves or wait for 1 process to complete for another processs to start.
Read locks don't block other readers. If you have on updates, you shouldn't have any blocking.
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
July 17, 2009 at 7:11 am
Tara (7/17/2009)
Here is one of the sample queries
drop table #empselect distinct EMPID
into #emp
from vwEmp_act
where (place IN ('21','25','51') or (empcd = '1' and jobcode = 'Y'))
AND empflag = 'F' and (servcdate between '1/1/2005' and '1/1/2009')
go
select distinct a.empidinto #step1_ct
from dbo.vwEmp as a inner join RevEmp.dbo.EmploerRaw as b
on a.empspec = b.speccode, #DateCriteria
where servcdate between serv_start and serv_end
and
(a.regid=b.regid)
and
(b.RegEmpSpec in (7,12))
Do you want advice on improving the performance of those queries? If so, can you post the entire procedures, table defs, index defs and execution plan please.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
July 17, 2009 at 7:18 am
other thing to mention is setting memory on the sql server box. our servers are dedicated to sql server and we set to use 90% of available memory for sql servers and also checkthe option of boos sql priority.
If i set the memory to use 50% only does the sql server stick to 50% or it can also use more then 50% when required?
If i need to test memory setting on sql server do i need to restart the service everytime of change settings?
July 17, 2009 at 7:26 am
Read locks don't block other readers. If you have on updates, you shouldn't have any blocking
as i said most of the time we only do SELECTS on the databases and i see SHAREDLOCK in the activity monitor, does it mean it is putting in wait for the second process to perform SELECT query.
does using READ UNCOMMITTED / NOCLOCK improves performance than Having shared lock.
July 17, 2009 at 7:29 am
Tara (7/17/2009)
Read locks don't block other readers. If you have on updates, you shouldn't have any blocking
as i said most of the time we only do SELECTS on the databases and i see SHAREDLOCK in the activity monitor, does it mean it is putting in wait for the second process to perform SELECT query.
does using READ UNCOMMITTED / NOCLOCK improves performance than Having shared lock.
Dont read through locks. There are exceptions , but as a rule of thumb dont.
Heres a good example why http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
Post the query plans Etc as suggested by Gail above....
July 17, 2009 at 7:36 am
Tara (7/17/2009)
Read locks don't block other readers. If you have on updates, you shouldn't have any blocking
as i said most of the time we only do SELECTS on the databases and i see SHAREDLOCK in the activity monitor, does it mean it is putting in wait for the second process to perform SELECT query.
As I said, read locks (ie locks taken by selects) do not block readers (ie other people doing selects)
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
July 17, 2009 at 7:42 am
I agree with Gail, hardware is not the place to start, especially since it appears that you've got a pretty strong system. Have you systematically collected performance data on the queries? Meaning, set up a server side trace, gather data over a period of time (I'd recommend a couple of days, make sure you have disk space available) and then aggregate that data to see which queries are taking the most time, etc.
Another approach is to gather wait states on the server. Find out what things are waiting for, disk, cpu, memory, and then determine what is waiting and what is causing the wait and go for the tuning from there.
Just because you're not getting index scans doesn't mean your queries are running optimally.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2009 at 8:05 am
Dave Ballantyne
I understand READ UNCOMITTED is exceptional as it gives inconsitent result but i think we dont worry about it as we do not update our productions so i think that should be fine as long as we get all the data very quick.
could some one please answer my question for memory settings on sql server
July 17, 2009 at 8:19 am
Tara (7/17/2009)
Dave BallantyneI understand READ UNCOMITTED is exceptional as it gives inconsitent result but i think we dont worry about it as we do not update our productions so i think that should be fine as long as we get all the data very quick.
Read uncommitted is only useful when selects are getting blocked by inserts/updates/deletes. If you have no inserts/updates/deletes, then you will have no blocking. Hence using read uncommitted will not get the data any faster.
could some one please answer my question for memory settings on sql server
What are your memory settings?
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
July 17, 2009 at 8:29 am
Tara (7/17/2009)
other thing to mention is setting memory on the sql server box. our servers are dedicated to sql server and we set to use 90% of available memory for sql servers
With 20GB of physical memory you should allow at least 3-4GB for the OS. Otherwise you risk high paging and instability from the OS
and also checkthe option of boos sql priority.
You should not need to use that and it's not always a good idea.
http://support.microsoft.com/kb/319942
If i set the memory to use 50% only does the sql server stick to 50% or it can also use more then 50% when required?
Whatever you set the max memory to is the max that SQL will use
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
July 17, 2009 at 9:17 am
The min memory setting is always 0 by default do i need to change that to work better.
If it is 0 does ti mean there no min value? what if i change that to use 5gb out of 20gb?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply