December 1, 2007 at 3:07 pm
Hi
Apologies for the long post, but I am in a bit of a scrape at the moment. I am doing a short SQL 2005 with SAP BW DBA contract overseas, and am encountering problems. I had told the agent that I don´t know either of these two, and only know SQL 2000, yet they still hired me.
I wonder whether I could ask for some thoughts about the following three problems before I give up and fly home.
The first problem is with tempdb. The client says that overnight there are five sets of data loads that run every hour from 1am to 5am. The data loads involve loading around 1 million records from a SAP R/3 system into "flat tables" in the SQL Server 2005 database. Then a second process merges these records into OLAP cubes in the database. Then a third process called "Activation" runs to recalculate the index values of these cubes.
The problem is that the size of tempdb sometimes grows to more than 15Gb during this process, and it runs out of disk space, causing the process to fail.
Is this large use of tempdb normal? If so how large could I expect it to get? Perhaps the solution is to ask the client to get a larger drive to hold tempdb - how large?
The second problem is more general, and is that a user says that when he runs a report, reading data from the cubes, it takes more than a minute to read the data out, even if it is only 40 rows that are returned.
Presumably this is because there are insufficient indexes on the tables, but there are apparently 120 tables involved with the cubes, and an amazing 15000 or so tables in total.
Also it seems to be the case that the SAP software itself creates and manages tables and indexes in the SQL database. Is this true? If so then presumably I would need to make any changes in the SAP software, and I know nothing about this.
The third problem is that they have shown me a printout from the SAP system that shows around 20 stored procedures that are taking between 30 seconds to 2 minutes to run. Again this is probably an index problem but I don´t see how I can resolve this if SAP handles the indexes.
Many Thanks
http://90.212.51.111 domain
December 1, 2007 at 3:58 pm
OK, you have 3 large questions and I'm not sure about #2. Need a BI/SSAS person to answer or give hints. I will say that you need to determine if the cubes are reading through to the tables. That could be a problem.
#1 - tempdb holds temporary objects, so sorts, aggregates, etc. There's no telling how large it needs to be other than to run the processes and see how large it gets. 15GB could be large or small, hard to tell.
Are all the data items (MDFs, LDFs, tempdb files) on one drive? During a load the log could be growing, so I'm asking to see if this is a problem as well. It sounds like the system isn't sized large enough.
Typically for a large system you'd like tempdb to be on its own drive/LUN/array for performance reasons. No idea how large to make this until you can get it to run and see how big it grows. Once you know, add a little pad and leave it there.
Same for the data and log files. They should be sizes large enough to handle the load with some space for growth built in. The log could be larger or smaller than the data, so don't be alarmed if it's large.
For #3, you need to actually profile the queries by running your own tests. You can execute them and get the actual plan chosen. There are also DMVs that can help you determine if your indexes are being used and find more.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply