7

I have noticed an interesting performance change that happens around 1,5 million entered values. Can someone give me a good explanation why this is happening?

Table is very simple. It is consisted of (bigint, bigint, bigint, bool, varbinary(max)) I have a pk clusered index on first three bigints. I insert only boolean "true" as data varbinary(max).

From that point on, performance seems pretty constant.

Legend: Y (Time in ms) | X (Inserts 10K)

enter image description here

I am also curios about constant relatively small (sometimes very large) spikes I have on the graph.

Actual Execution Plan from before spikes.

Actual Execution Plan from before spikes

Legend:
Table I am inserting into: TSMDataTable
1. BigInt DataNodeID - fk
2. BigInt TS - main timestapm
3. BigInt CTS - modification timestamp
4. Bit: ICT - keeps record of last inserted value (increases read performance)
5. Data: Data
Bool value Current time stampl keeps

Enviorment
It is local.
It is not sharing any resources.
It is fixed size database (enough so it does not expand).
(Computer, 4 core, 8GB, 7200rps, Win 7).
(Sql Server 2008 R2 DC, Processor Affinity (core 1,2), 3GB, )

Falcon
  • 620
  • 1
  • 8
  • 24
  • 1
    spikes will presumably be the SQL lazy writer – Mitch Wheat Sep 13 '11 at 09:28
  • BTW, a clustered index on 3 bigints is a bad idea. – Mitch Wheat Sep 13 '11 at 09:29
  • 1
    Why is that? What would you propose? – Falcon Sep 13 '11 at 09:30
  • 1
    http://mitch-wheat.blogspot.com/2011/08/sql-server-is-it-ok-to-use.html – Mitch Wheat Sep 13 '11 at 09:31
  • @Mitch Wheat Nice article. Gave me some ideas. Do you have any this valuable resources on lazy writing? – Falcon Sep 13 '11 at 12:16
  • 1
    The lazy writer is a normal SQL server process (i.e. it's how SQL is designed to work): http://sqlserverpedia.com/blog/sql-server-bloggers/the-lazy-writer-and-the-checkpoint/ – Mitch Wheat Sep 13 '11 at 13:02
  • Can you [get an actual execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) from before / after the spike? – Justin Sep 27 '11 at 11:37
  • @Justin voila - does it help? – Falcon Sep 27 '11 at 12:16
  • There are way too many variables in this to give a proper guesstimate. What hardware is this running on? Local disks or a SAN? What disk layout? Is _any_ (disks, CPU, network, etc) shared by anything else but your experiment? Is SQL Server doing anything but running your test? Are your data files configured with a proper size or do they auto expand? If so, using percentages or fixed sizes? In short, this question is probably more suited for http://dba.stackexchange.com/, but really, you need to provide a plethora of extra information for anyone to venture a guess. – Mark S. Rasmussen Sep 27 '11 at 11:40
  • It is local. It is not sharing any resources. It is fixed size (enough so it does not expand). (4 core, 8GB, 7200rps, Win 7). (Sql Server 2008 R2 DC, 3GB, Processor Affinity (core 1,2)). – Falcon Sep 27 '11 at 12:11
  • Do you know if you're CPU or IO bound? Is CPU at 100% all the way through, or does it fluctuate? Can you monitor the PhysicalDisk\Disk Transfers/Sec performance counter during the experiment? Perhaps also monitor the SQL Server Transactions/Sec counter. This should give an idea of the bottleneck and enable us to further pinpoint the cause. – Mark S. Rasmussen Sep 27 '11 at 13:20
  • @Mark S. RasmussenNon of the resources is running at 100% or near. Only SqlServer is using its 3GB. How do you suggest to monitor all (insert perfromance, cpu usage, and io)? – Falcon Sep 27 '11 at 13:40
  • 1
    A possible options is. Your disk have a disk cache. If the SQL-Server writeback and the disk buffer runs full, then the write performance slow down to the real disk I/O-Performance. – edze Sep 27 '11 at 15:00
  • @edze So when lazy writer tries to write everything at once, it writes to the disk, but disk cache is full, so everything slows down? – Falcon Sep 27 '11 at 15:06
  • @Gonsalu - I think you have not read the description and comments. Database space is prealocated, and it is (Y - time for 10k inserts), (X - no of inserted values) – Falcon Sep 29 '11 at 12:55
  • Are the three bigints you have on the primary key generated sequentually? ie. Does the value get bigger for each big int on the next insert statement ? – user957902 Oct 01 '11 at 15:06
  • @user957902 - First bigint is a ref, other two increment. – Falcon Oct 03 '11 at 08:48
  • What do you mean "Sql Server 2008 R2 DC"? Are you using Datacenter Edition (and if so, why?) or running it on a Domain Controller? – Brent Ozar Oct 07 '11 at 12:59
  • @Brent Ozar - DC - Data Center - why, because it is either this or Express. Pragmatic problem. – Falcon Oct 07 '11 at 16:10
  • 2
    @Falcon - err, Datacenter Edition is $60,000 per CPU. You shouldn't be running that on a box with only one socket, especially not if you're affinitizing it to just two cores of the CPU. It's one of those data points (DC + affinity masking) that makes me think there's probably other odd hardware/software config issues involved. – Brent Ozar Oct 11 '11 at 12:54
  • @Brent Ozar - Thank you for pointing that out. I will take a look at it. – Falcon Oct 11 '11 at 15:01

1 Answers1

1

Have you checked the execution plan once the time goes up? The plan may change depending on statistics. Since your data grow fast, stats will change and that may trigger a different execution plan.

Nested loops are good for small amounts of data, but as you can see, the time grows with volume. The SQL query optimizer then probably switches to a hash or merge plan which is consistent for large volumes of data.

To confirm this theory quickly, try to disable statistics auto update and run your test again. You should not see the "bump" then.

EDIT: Since Falcon confirmed that performance changed due to statistics we can work out the next steps.

I guess you do a one by one insert, correct? In that case (if you cannot insert bulk) you'll be much better off inserting into a heap work table, then in regular intervals, move the rows in bulk into the target table. This is because for each inserted row, SQL has to check for key duplicates, foreign keys and other checks and sort and split pages all the time. If you can afford postponing these checks for a little later, you'll get a superb insert performance I think.

I used this method for metrics logging. Logging would go into a plain heap table with no indexes, no foreign keys, no checks. Every ten minutes, I create a new table of this kind, then with two "sp_rename"s within a transaction (swift swap) I make the full table available for processing and the new table takes the logging. Then you have the comfort of doing all the checking, sorting, splitting only once, in bulk.

Apart from this, I'm not sure how to improve your situation. You certainly need to update statistics regularly as that is a key to a good performance in general.

Might try using a single column identity clustered key and an additional unique index on those three columns, but I'm doubtful it would help much.

Might try padding the indexes - if your inserted data are not sequential. This would eliminate excessive page splitting and shuffling and fragmentation. You'll need to maintain the padding regularly which may require an off-time.

Might try to give it a HW upgrade. You'll need to figure out which component is the bottleneck. It may be the CPU or the disk - my favourite in this case. Memory not likely imho if you have one by one inserts. It should be easy then, if it's not the CPU (the line hanging on top of the graph) then it's most likely your IO holding you back. Try some better controller, better cached and faster disk...

Rbjz
  • 2,057
  • 1
  • 21
  • 37
  • I will take a look at it and let you know what I found. – Falcon Oct 03 '11 at 08:49
  • 1
    I have tested it, and after turning off the statistics, I did not see the "bump". Through out the test results were very consistent, and I have noticed significant - 10% - increase in performance. – Falcon Oct 20 '11 at 09:47
  • 1
    Very well Falcon :) happy. Now you know what's happening and you may be able to fix that ;) I'll try to give some suggestions editing my answer. – Rbjz Dec 08 '11 at 19:55
  • Thanks for additional info. I will definitely take a deeper look into it. – Falcon Dec 16 '11 at 14:46