Anyone direct me to a good admin overview? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Anyone direct me to a good admin overview?

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-20 : 12:45:03

I'm sure my company does not have sql server configured and set up as good as it can be. I have problems when we are running one or two queries along with a couple users accessing functions.

Can anyone direct me to some material that can help me optomize things so there won't be interuptions? One example of the performance issues was there were two of us in IT running queries on SQL server along with a user trying to connect to business objects to run reports. Our queries caused the user to time out. I do notice that there are several "threads" running to produce the queries.

Thanks for your help!

Marcie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-20 : 12:48:45

You could check out sql-server-performance.com, but I'd suggest just posting the problematic queries, plus the tables involved including their indexes so that we can help you out.

Tara Kizer

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-20 : 13:12:01

Tara - It's not necessarily any one query. I cannot run queries,stored procedures, programs without interfering with production users. Is there a way to lower priority for specific users so the production work gets higher priority? I'm not sure what I should look at to correct this. Maybe we need to bring a consultant in to tell us how to set it up.

We've been using sql server about a year now, but we are just starting to move more users and processes from our MYsql database to sql server.

Marcie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-20 : 13:15:12

There is no way to do this priority thing. In order for us to help we'd need to see some of the queries and the database design.

Tara Kizer

Sitka
Aged Yak Warrior

571 Posts

Posted - 2006-09-20 : 14:17:06

quote:
I'm sure my company does not have sql server configured and set up as good as it can be

If it works at all there is not near as much reason to suspect the configuration as there is to SUSPECT the hardware/OS or the applications or business objects. You could be the exception, of course.

Measure twice, cut once.

Start by measuring the time it takes to run simple statements in Query Analyser. Select * from <some table>. Many of the tables should return instantly.

or do this one

SELECT name, id, xtype, uid, info, status, base_schema_ver, replinfo, parent_obj, crdate, ftcatid, schema_ver, stats_schema_ver, type, userstat, sysstat, indexdel, refdate, version, deltrig, instrig, updtrig, seltrig, category, cache FROM master.dbo.sysobjects

report back the time beside the row count

quote:

I cannot run queries,stored procedures, programs without interfering with production users.

cannot!!!!
means you are using a Pentium 166/64 MB minimum, lets assume that is not the case?

"it's definitely useless and maybe harmful".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-20 : 14:22:24

I would not suggest running select * from <some table> on a table that has a lot of rows. You should use top 1000 or so for larger tables. We make it a habit to always use top 1000 in our production environment if we aren't going to narrow down our results with a where clasue.

Tara Kizer

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-20 : 22:19:31

I did find out some more info. I was seeing multiple cxpacket waits in Enterprise manager. Two of the queries that were running today and causing issues were accessing two databases. Is this a bad practice? It seems to me like it is because of the performance hits and time outs I was seeing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-20 : 23:45:28

We do queries across databases without any performance problems.

Could you post these two queries so that we can review and help out?

Tara Kizer

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-21 : 08:48:11

Here is one query. The one that I personally was working on was a report through crystal reports.

set DECLARE @useDatePrimary datetime
set DECLARE @useDateSecondary datetime

set @useDatePrimary= '2006/08/31'
set @useDateSecondary= '2006/09/01'

select

all_provs.prov_num,

all_provs.full_name,

mailing_info.tax_id,

all_provs.indicator as wppn_OR_hcn_OR_ahc_network,

heos_provs.indicator as heos_network,

chiro_provs.indicator as specialty_chiro,

provider_claims.claim_count as claim_count,

provider_claims.charge as charge,

provider_claims.price as price,

MAX

(

(

case when pdeals.net_term is null

then '2050/01/01'

else pdeals.net_term

end

)

) as max_term,

address.addr_num,

address.addr_addr1,

address.addr_addr2,

address.addr_city,

addr_state,

addr_zip,

addr_count,

mailadrs.mail_addr1,

mailadrs.mail_addr2,

mailadrs.mail_city,

mailadrs.mail_state,

mailadrs.mail_zip,

mailadrs.mail_contf,

mailadrs.mail_contl

--into Temp_tables.dbo.ttp3344_091406_5

from

(

select distinct provider.prov_num,provider.full_name,'X' as indicator

from provider

join pdeals on pdeals.deal_under= provider.prov_num

where

(pdeals.net_term >= @useDatePrimary or pdeals.net_term is null)

and pdeals.net_work in (2,3,7,15,101,108,110,114,115,126,131,133,143,144,151,152,162,164,166,168,169,171,183,184)

) as all_provs

left join

(

select distinct provider.prov_num,'X' as indicator

from provider

join pdeals on pdeals.deal_under= provider.prov_num

where

(pdeals.net_term >= @useDateSecondary or pdeals.net_term is null)

and pdeals.net_work in (1,125,127,138,172,173,175,195,204,205,218,229,231,232,561)

) as heos_provs

on all_provs.prov_num=heos_provs.prov_num

left join

(

select distinct provider.prov_num,'X' as indicator

from provider

join provsp on provsp.prov_num= provider.prov_num

where

provsp.special_nu=7

) as chiro_provs

on all_provs.prov_num=chiro_provs.prov_num

join

(select

passn.owner_num,

passn.addr_num,

passn.tax_id,

count(distinct passn.prov_num) as docs,

max(max_docs.counted) as max_docs

from passn

join (

select

passn.owner_num,

passn.addr_num,

count(distinct passn.prov_num) as counted

from passn

where

(passn.deal_term is null or passn.deal_term >= @useDatePrimary)

and passn.deal_eff is not null

group by passn.owner_num,passn.addr_num

) as max_docs

on max_docs.owner_num= passn.owner_num

where

(passn.deal_term is null or passn.deal_term >= @useDatePrimary)

and passn.deal_eff is not null

group by passn.tax_id,passn.owner_num,passn.addr_num

having count(distinct passn.prov_num)=max(max_docs.counted)

) as mailing_info

on mailing_info.owner_num= all_provs.prov_num

join address

on address.addr_num= mailing_info.addr_num

left join mailadrs

on all_provs.prov_num= mailadrs.prov_num

join pdeals

on pdeals.deal_under= all_provs.prov_num

left join

(

SELECT

dtbl_claims.prov_num,

count(distinct dtbl_claims.claim) as claim_count,

sum(charge) as charge,

sum(repriced_amt) as price

FROM

[HMPDW].dbo.dtbl_CLAIMS dtbl_CLAIMS

where

dtbl_CLAIMS.datein >= '2005/08/31'

and dtbl_CLAIMS.proc_date >= '2005/08/31'

and dtbl_CLAIMS.source!='D'

and dtbl_claims.net_num in (1,125,127,138,172,173,175,195,204,205,218,229,231,232,2,3,7,15,101,108,110,114,115,126,131,133,143,144,151,152,162,164,166,168,169,171,183,184)

group by

dtbl_claims.prov_num

) as provider_claims

on all_provs.prov_num= provider_claims.prov_num

where

pdeals.net_work in (1,125,127,138,172,173,175,195,204,205,218,229,231,232,2,3,7,15,101,108,110,114,115,126,131,133,143,144,151,152,162,164,166,168,169,171,183,184)

AND (pdeals.net_term >= @useDatePrimary or pdeals.net_term is null)

and heos_provs.indicator is null

group by

all_provs.prov_num,

all_provs.full_name,

mailing_info.tax_id,

all_provs.indicator,

heos_provs.indicator,

chiro_provs.indicator,

address.addr_num,

address.addr_addr1,

address.addr_addr2,

address.addr_city,

addr_state,

addr_zip,

addr_count,

mailadrs.mail_addr1,

mailadrs.mail_addr2,

mailadrs.mail_city,

mailadrs.mail_state,

mailadrs.mail_zip,

mailadrs.mail_contf,

mailadrs.mail_contl,

provider_claims.claim_count,

provider_claims.charge,

provider_claims.price

having

MAX (

case when pdeals.net_term is null

then '2050/01/01'

else pdeals.net_term

end

) in ('2050/01/01','2006/08/31')

order by provider_claims.charge desc

Thanks,
Marcie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-21 : 12:49:23

Perhaps you could format the code for us a bit. But just from the looks of it, it needs to be rewritten. It's a monstrous query and I don't mean by size.

Tara Kizer

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-21 : 13:40:24

Sorry about that. Hopefully this is a little easier to read.
[CODE]
set DECLARE @useDatePrimary datetime
set DECLARE @useDateSecondary datetime
set @useDatePrimary= '2006/08/31'
set @useDateSecondary= '2006/09/01'

select all_provs.prov_num,
all_provs.full_name,
all_provs.indicator as wppn_OR_hcn_OR_ahc_network,
heos_provs.indicator as heos_network,
heop_provs.indicator as heop_network,
chiro_provs.indicator as specialty_chiro,
provider_claims.claim_count as claim_count,
provider_claims.charge as charge,
provider_claims.price as price,
MAX((case when pdeals.net_term is null then '2050/01/01'
else pdeals.net_term end )) as max_term,
address.addr_num,
address.addr_addr1,
address.addr_addr2,
address.addr_city,
addr_state,
addr_zip,
addr_count,
mailadrs.mail_addr1,
mailadrs.mail_addr2,
mailadrs.mail_city,
mailadrs.mail_state,
mailadrs.mail_zip,
mailadrs.mail_contf,
mailadrs.mail_contl
--into mb_ttp3344_091406_4
from
(select distinct provider.prov_num,provider.full_name,'X' as indicator
from provider
join pdeals on pdeals.deal_under= provider.prov_num
where(pdeals.net_term >= @useDatePrimary or
pdeals.net_term is null)
and peals.net_work in
(2,3,7,15,101,108,110,114,115,126,131,133,143,
144,151,152,162,164,166,168,169,171,183,184)) as all_provs

left join
(select distinct provider.prov_num,'X' as indicator
from provider
join dbo.pdeals on pdeals.deal_under= provider.prov_num
where(pdeals.net_term >= @useDateSecondary
or pdeals.net_term is null)
and pdeals.net_work in
(1,125,127,138,172,173,175,195,204,205,218,229,231,232,561))
as heos_provs
on all_provs.prov_num=heos_provs.prov_num

left join
(select distinct provider.prov_num,'X' as indicator
from provider
join pdeals on pdeals.deal_under= provider.prov_num
where (pdeals.net_term >= @useDateSecondary or
pdeals.net_term is null)
and pdeals.net_work=561) as heop_provs
on all_provs.prov_num=heop_provs.prov_num

left join
(select distinct provider.prov_num,'X' as indicator
from provider
join provsp on provsp.prov_num= provider.prov_num
where provsp.special_nu=7) as chiro_provs
on all_provs.prov_num=chiro_provs.prov_num

join
(select passn.owner_num,
passn.addr_num,
count(distinct passn.prov_num) as docs,
max(max_docs.counted) as max_docs
from passn

join (select passn.owner_num,
passn.addr_num,
count(distinct passn.prov_num) as counted
from passn
where (passn.deal_term is null or
passn.deal_term >= @useDatePrimary)
and passn.deal_eff is not null
group by passn.owner_num,passn.addr_num) as max_docs
on max_docs.owner_num= passn.owner_num
where (passn.deal_term is null or
passn.deal_term >= @useDatePrimary)
and passn.deal_eff is not null
group by passn.owner_num,passn.addr_num
having count(distinct passn.prov_num)=
max(max_docs.counted)) as mailing_info
on mailing_info.owner_num= all_provs.prov_num
join address on address.addr_num= mailing_info.addr_num
left join mailadrs on all_provs.prov_num= mailadrs.prov_num
join pdeals on pdeals.deal_under= all_provs.prov_num
left join
(SELECT master_combined.prov_num,
count(distinct master_combined.claim) as claim_count,
sum(charge) as charge,
sum(price) as price
FROM master_combined join
detail_combined on detail_combined.claim =
master_combined.claim
and detail_combined.version =
master_combined.version
and detail_combined.source = master_combined.source
where master_combined.datein >= '2005/08/31'
and master_combined.proc_date >= '2005/08/31'
and master_combined.source!='D'
group by master_combined.prov_num)
as provider_claims
on all_provs.prov_num= provider_claims.prov_num

where pdeals.net_work in
(1,125,127,138,172,173,175,195,204,205,218,229,
231,232,2,3,7,15,101,108,110,114,115,126,131,
133,143,144,151,152,162,164,166,168,169,171,183,184)
AND (pdeals.net_term >= @useDateSecondary or
pdeals.net_term is null)
group by
all_provs.prov_num,
all_provs.full_name,
all_provs.indicator,
heos_provs.indicator,
heop_provs.indicator,
chiro_provs.indicator,
address.addr_num,
address.addr_addr1,
address.addr_addr2,
address.addr_city,
addr_state,
addr_zip,
addr_count,
mailadrs.mail_addr1,
mailadrs.mail_addr2,
mailadrs.mail_city,
mailadrs.mail_state,
mailadrs.mail_zip,
mailadrs.mail_contf,
mailadrs.mail_contl,
provider_claims.claim_count,
provider_claims.charge,
provider_claims.price
having
MAX (case when pdeals.net_term is null then '2050/01/01'
else pdeals.net_term end )
in ('2050/01/01','2006/08/31')[/CODE]

Marcie

Kristen
Test

22859 Posts

Posted - 2006-09-21 : 14:35:15

"We make it a habit to always use top 1000 in our production environment if we aren't going to narrow down our results with a where clasue"

We actually do this on all queries on production (i.e. regardless of WHERE clause) just in case we make a typo in the where clause and get everything / too much by accident!

memarcie: Suggest you re-edit your last and put a [CODE] tag at the start, and a [/CODE] at the end - its already correctly formatted, just without the CODE tag it doesn't display that way.

Where to start? [;-)]

Lots of very inefficient code in this query. All the SELECT DISTINCTS are going to have to find the relevant rows, sort them, remove the duplicates, and then use the results. Get rid of the DISTINCTS - i.e. reform the queries so that they naturally only return unique rows - perhaps by using EXISTS instead of JOINs

Why do you need Sub-Selects, rather than just JOINing the tables? (I haven't looked carefully, but a direct JOIN will restrict the number of rows very quickly, a JOIN of two sub-selects is almost certainly going to mean that the Sub-Selects pull all possible rows, and then attempt the JOIN - throwing away a good proportion of the rows I would guess.

Actually looking at it a bit closer it looks as though you may be doing a Cross Tab - if that's the case there are articles here about ways to do that, which would be far more efficient. if not, then it may help to pre-select the data from [provider] which you are repeatedly selecting, and then use that temporary table in your main query to reduce the repetitivness.

from dbo.provider

always specify the table owner - it helps to get the query cached.

set @useDatePrimary= '2006/08/31'

ALWAYS use the only unambiguous format for date constants which is yyyymmdd - e.g. '20060831'

Lastly you should check the query plan for the various individual bits [and/or the whole lot] of the query and try creating indexes etc. to improve the performance of the query.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-09-24 : 10:35:08

what does the
SELECT name, id, xtype, uid, info, status, base_schema_ver, replinfo, parent_obj, crdate, ftcatid, schema_ver, stats_schema_ver, type, userstat, sysstat, indexdel, refdate, version, deltrig, instrig, updtrig, seltrig, category, cache FROM master.dbo.sysobjects

help you find...i just run it what should we look at.
Cheers

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-09-25 : 08:45:26

It creates one line for each object created within a database, I prefer to use information schema views myself though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources