August 8, 2008 at 10:52 pm
How is this query filling the transaction logs when it is a SELECT statement?
Which database is experiencing the transaction log issues? Is it BAP_PROD_2006 or the database you are connected to?
An example of a covering index for BAP_PROD_2006.dbo.cust_agrmnt_dim would be:
CREATE INDEX NCIX_BAP_PROD_2006_COVERING_01
ON (CUST_AGRMNT_NUM,
August 8, 2008 at 11:08 pm
Finishing my premature post :).
How is this query filling the transaction log when it is a SELECT statement? Which database is experiencing the transaction log issues? Is it BAP_PROD_2006 or the database you are connected to?
Here is an example of a covering index for BAP_PROD_2006.dbo.cust_agrmnt_dim:
CREATE NONCLUSTERED INDEX NCIX_BAP_PROD_2006_COVERING_01
ON BAP_PROD_2006.dbo.cust_agrmnt_dim
(CUST_AGRMNT_NUM,
TIME_KEY,
CUST_AGRMNT_TYPE,
ACTIVE_ROW_IND)
WITH (FILLFACTOR = 100,
SORT_IN_TEMPDB = ON)
Only use the SORT_IN_TEMPDB = ON if tempdb is isolated to it's own disk or disk array. This option will utilize tempdb for sort space while building the index.
This index "covers" the SELECT, FROM (JOIN), and WHERE clause columns for your query. The above index includes all of the columns referenced in your query for the table BAP_PROD_2006_CO. This nonclustered covering index is a separate entity on disk from the heap (table) structure; the use of a covering index will allow the query to complete without performing I/O against this particular table. You should notice that the operation referenced in the Estimated Execution Plan should change from a Table Scan to an Index Scan. Warning: if you do not cover all of the columns in the query (SELECT, FROM, WHERE) then you will end up with a RID Lookup operation against the table (heap) itself.
If you were to cover each of the heaps involved in the query (all of the BAP_PROD_2006 tables?) with a similar index, it should allow you to execute the query without performing I/O directly against the heaps.
I hope this helps.
Ken Garrett
August 11, 2008 at 8:27 am
Hi Ken,
So Table scan is the worst.
RID is next to Table scan.
And the goal is to switch to Index scan.
Am I correct?
BAP_PROD_2006_Log.LDF (Transaction Log of BAP_PROD database)
was growing.
BAP_PROD_2006 is an archive database.
They normally don't join current data in BAP_PROD with the archived data.
But the business wants do some investigation and they need to recover some missing data using BAP_PROD_2006.
I forgot to mention.
It's a Data Warehouse environment. Sort of.
They keep staging tables and facts and dimensions in one database. I'm new to this company. My task is to build an automated ETL process using SSIS but the more I work with data the more I see strange database design things, low disk space issues, lack of monitoring, alerts. The boss told me that together with SSIS he wants me to look at the overall database design, DBA related problems.
They're going to change data model in 2 months:
1 Separate staging tables (load from ETL),
2. Have facts and dimensions tabales in a separate datawarehouse database.
3. Create new tables in staging and datawarehouse databases
They even want to call the databases:
Staging
Warehouse
While I'm working on ETL I have to look at their existing design and make suggestions.
August 11, 2008 at 8:33 am
riga1966 (8/8/2008)
So this Execution Plan I posted is not good enough?
It can be worked with, but a plan saved as a plan gives a lot more information and is quite useful in this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 10:05 am
Hi riga1966,
How was the transaction log growing for a SELECT statement? My guess is that you had a concurrent DML or DDL transaction running in the BAP_Prod_2006 database.
If you use fully covering indexes on all of the BAP_Prod_2006 tables (heaps w/o clustered indexes) you should eliminate all of the Table Scan operations against the BAP_Prod_2006 database.
Note: A RID Lookup operations generally happens because a nonclustered index does not cover all of the columns used in a SQL statement. The RID Lookup occurs when the query has to go back to the heap (table w/o clustered index) to retrieve column values not specified in the nonclustered index.
I think that separating the Staging tables from the Fact and Dimension tables is a good thing. As GSquared mentioned earlier in this succession of posts, I think having a Clustered Index on tables (even in the Staging database) should be considered as a new table design standard.
You also may want to consider placing the the Staging and Warehouse databases on different disks or disk arrays. That way the read I/O against Staging will not contend with the write I/O against the Warehouse when the Warehouse ETL process is running.
Again, I hope this info helps. Good luck (no sarcasm intended)!
Ken Garrett
August 11, 2008 at 11:10 am
Ken,
Sorry I didn't mention it earlier.
This was INSERT ... SELECT FROM ...kind of statement that filled up transaction log.
Because it was causing problems I killed the transaction
and troubleshooted the SELECT part of that INSERT
and found that the problem was with SELECT as well.
It was taking forever to even return COUNT(*) of records.
August 11, 2008 at 12:15 pm
riga1966,
You may want to run the query using SSIS to limit the batch size. 14 GB sounds like a pretty good sized transaction log. How large is the data file for BAP_Prod_2006 database?
I think you did the right thing by looking at the Estimated Execution Plan of the SELECT statement in isolation. If you use covering indexes on all of the BAP_Prod_2006 tables and use SSIS to limit the batch size, you may be able to get this query to complete in a reasonable time frame.
Ken Garrett
August 11, 2008 at 12:36 pm
Hi Ken,
The size of the data file for BAP_PROD_2006
is 33 GB.
Ken,
How exactly SSIS helps to reduce bacth size
and what is that "batch size"?
What does it mean?
Could you tell a few words about it?
Thanks
August 11, 2008 at 3:18 pm
Hi riga1966,
A 14GB transaction log is extremely large for a 33GB datafile. Is 33GB the total of all datafile space for the database? You may need to ensure that you have enough free space in the data file(s) before attempting to rerun your INSERT query.
Batch size is delineated using the number of rows. ETL tools like DTS, SSIS, or Business Objects Data Integrator will typically allow you to specify a batch size when inserting data into a target or destination table. A COMMIT statement is executed after the number of rows specified in the batch size has been inserted into the target or destination table.
Ken Garrett
August 11, 2008 at 8:19 pm
I haven't read all the posts on this thread, but I have to ask... why do you want to return 200k+ rows for anything? :blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2008 at 8:00 am
Old Hand: you have been - as I like to put it - flopping-and-twitching on this issue for what, 5 days + now. :w00t: May I recommend a short engagement with a tuning professional who can both get your current problems resolved and also mentor you on how to solve them more effectively in the future? You will be quite amazed at how much you don't know at this point that will help you out and a good consultant can get you up to speed quickly.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 12, 2008 at 9:07 am
Also, looking at the query plan you posted, looks like you're going into some massive parallelism on that query. Depending on what's going on, that could potentially kill your query performance due to marshalling of worker threads.
Suggest running your query using WITH (MAXDOP = 1) or setting the SQL Server value for 'cost threshold for parallelism' from 5 seconds to something more realistic like 20 seconds or so.
But as stated before... how is a return of over 20 million records even useful to the operator? Surely they are going to do further processing of that data to make it "business friendly" and if so, wouldn't it make sense to work that into the query? I highly doubt a client app will have the processing power your SQL Server has to do that. Especially if they're using something like MS Access or Excel to cut the data apart.
August 12, 2008 at 9:20 am
Jeff Moden (8/11/2008)
I haven't read all the posts on this thread, but I have to ask... why do you want to return 200k+ rows for anything? :blink:
He mentioned late in the thread that it's an Insert...Select. He's not returning them, he's loading them into a table.
I've had plenty of warehousing solutions that had to deal with millions of rows at a time. I think that's what's going on here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 12, 2008 at 6:23 pm
Ah... thanks Gus.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply