As with any RDBMS, or, at least, SQL DBMS, mixing operational statements
with reporting ones is a formula for disaster. Now, the various
platforms will handle the locking behavior slightly differently, the
primary tenet is still valid.
With the number of rows you are quoting, forget about the database size,
the table size is more relevant. You want to limit reporting queries
during OLTP operations and, conversely, you should not be running OLTP
processes against a reporting, OLAP, or data mining solution except for
the batch loads that occur during off hours.
If you have not separated out this functionality, consider archiving the
data to a database copy and/or warehouse/data mart. If have eliminated
that possibility--you've indicated no blocking--then it is to the query
expected and realized execution plans you must consider. Using Query
Analyzer, you can examine all of these elements as well as the
availability of statistics and optimizer index selection, or the lack
there of.
It is immaterial whether or not the queries are built on stacks of VIEWS
or written directly to the base tables, the optimizer will choose the
most efficient execution plan given appropriate indexing and statistics
generation. The only thing that may hamper--or actually help--is
whether or not you've materialized any of those VIEWs through the use of
indexes on the VIEWs themselves.
Without any of this information provided to this forum, it would be
impossible for us to make any other recommendation. If you where to
provide some of these diagnostics, we may be able to provide more clear
cut opinions.
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Johnson Mark E
[mailto:mssqldba-ezmlmshield-x21281899.[Email address protected]
Sent: Tuesday, August 24, 2004 5:51 PM
To: LazyDBA Discussion
Subject: RE: Help with locks, pauses
Good point, if the views behave badly so will the query. If the views
are the problem, you might be able to index them. Look at your I/O, and
paging etc as well.
Mark
________________________________
From: Christopher P. Barnes
[mailto:mssqldba-ezmlmshield-x61430279.[Email address protected]
Sent: Tue 8/24/2004 5:32 PM
To: LazyDBA Discussion
Subject: RE: Help with locks, pauses
"It only seems to issue Schema Stability locks when running (because it
is built on a stack of views)."
Did you profile the views that the query is based on?
-Chris
-----Original Message-----
From: Johnson Mark E
[mailto:mssqldba-ezmlmshield-x52567857.[Email address protected]
Sent: Tuesday, August 24, 2004 6:34 PM
To: LazyDBA Discussion
Subject: RE: Help with locks, pauses
If your not seeing any blocking, why do you think locking is the
problem?
Mark
________________________________
From: Gwr2435 [mailto:mssqldba-ezmlmshield-x82727449.[Email address
protected]
Sent: Tue 8/24/2004 5:03 PM
To: LazyDBA Discussion
Subject: Help with locks, pauses
We have a fairly large SqlServer2000 DB (~30GB), some tables have
10million to 100million rows. Raid5, 4way, 4GB RAM, tempdb and
transaction logs on separate disks. This is an extremely active db --
mostly batch processing.
We have some queries that run several times per day. The same query,
which usually returns 10-20 rows, can take anywhere from 1 minute to 4+
hours. In Enterprise Manager, it doesn't say anything is blocking it.
It only seems to issue Schema Stability locks when running (because it
is built on a stack of views).
We've tried with(readpast), with(NOLOCK), SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITED. It just seems that *something* else is happening
that is prevents the query from finishing sometimes. Could it be
something like MSSQL Server is updating it's own statistics or something
and that is interfering with the SELECT statment?
I'm from an Oracle background and I've never seen other activity block a
SELECT statement like this. I'm not trashing SQLServer, I just want to
know what the key is to preventing this behavior. I must be missing
something...
Thanks,
Glen Roberts
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************
MS Sql Server LazyDBA home page