November 18, 2014 at 2:30 pm
Hi folks,
I'm running Microsoft Retail Management System as our POS solution across 20+ stores.
These stores upload sales data every hour one after the other separated by 2 minutes (store 1 @ 1:02, store 2 @ 1:04, etc.) to an application acting as the middle man between the stores and the headquarters database running on SQL 2005.
As the database is growing, it takes more and more time for these stores to complete uploading sales data.
I wonder if SQL Profiler is able to pinpoint which table causes the bottle neck.
In other words, which table this "middle-man application" takes most of the time connected.
Please advise.
Thanks,
...Alex
November 19, 2014 at 4:15 am
Has the amount of data increased over time? More data generally means more time to execute. If possible try archiving old data that is not needed anymore. Or seperate actual and older data into seperate tables/schemas/databases... Only transfer the data needed (and don't include data that hasn't changed since the previous transfer).
You could use profiler to trace all actions being executed. In profiler you can include the column "duration" to see how long it took to complete an action.
Setup a serverside profiler trace. Make sure to select as few events as possible (most likely only the "RPC/batch/stmt completed" commands) and only select the columns (like SPID, textdata, duration, CPU, reads, writes) you need. Also filter as narrow as possible (use the databasename and/or hostname and/or loginname). The smaller your profiler trace is, the less performance impact it will have. Also the generated data will be more easy to analyse. Finally keep the trace only running during the period you need to investigate.
November 19, 2014 at 8:39 am
Hi SSCrazy,
Yes, definitely, data has increase and will increase over time.
Will run SQL Profiler and share results with you.
Since this is a Microsoft solution, I assume Microsoft should be able to say which tables can be warehoused, right?
Thanks for your assistance.
...Alex
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply