SQL 2005 write performance

Question

Post reply

SQL 2005 write performance

leonp

SSCommitted

Points: 1962
More actions
December 15, 2008 at 2:42 pm

#216590

Hello,

I am seeing a write bottleneck in my application that writes out to a SQL 2005 database. The application is issuing commits every 10,000 rows and each row is ~1KB. Below are a couple of sysperfinfo snapshots I could gather from the database when the app was running. I am led to believe that there is both a memory and disk bottleneck here as the Log Cache Hit Ratio seems to be falling and the subsequent increased log flushes also seem to be having some wait. I can also see a low PLE. However, I do not consider myself the expert with these numbers and hence the request for some expert opinion on our numbers here. Any help you could provide would be truly appreciated!

[font="Courier New"]object_name counter_name instance_name cntr_value cntr_type

----------------------------- ------------------------------- --------------- ----------- -----------

SQLServer:Buffer Manager Page life expectancy 282 65792

SQLServer:Buffer Node Page life expectancy 000 282 65792

SQLServer:Databases Data File(s) Size (KB) CREMS_IBNR_DB 85522112 65792

SQLServer:Databases Log File(s) Size (KB) CREMS_IBNR_DB 9293944 65792

SQLServer:Databases Log File(s) Used Size (KB) CREMS_IBNR_DB 43796 65792

SQLServer:Databases Percent Log Used CREMS_IBNR_DB 0 65792

SQLServer:Databases Active Transactions CREMS_IBNR_DB 1 65792

SQLServer:Databases Transactions/sec CREMS_IBNR_DB 1388 272696576

SQLServer:Databases Repl. Pending Xacts CREMS_IBNR_DB 0 65792

SQLServer:Databases Repl. Trans. Rate CREMS_IBNR_DB 0 272696576

SQLServer:Databases Log Cache Reads/sec CREMS_IBNR_DB 11201644 272696576

SQLServer:Databases Log Cache Hit Ratio CREMS_IBNR_DB 10918669 537003264

SQLServer:Databases Log Cache Hit Ratio Base CREMS_IBNR_DB 11201644 1073939712

SQLServer:Databases Bulk Copy Rows/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases Bulk Copy Throughput/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases Backup/Restore Throughput/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases DBCC Logical Scan Bytes/sec CREMS_IBNR_DB 565248 272696576

SQLServer:Databases Shrink Data Movement Bytes/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases Log Flushes/sec CREMS_IBNR_DB 414883 272696576

SQLServer:Databases Log Bytes Flushed/sec CREMS_IBNR_DB 25299399168 272696576

SQLServer:Databases Log Flush Waits/sec CREMS_IBNR_DB 1376 272696576

SQLServer:Databases Log Flush Wait Time CREMS_IBNR_DB 0 65792

SQLServer:Databases Log Truncations CREMS_IBNR_DB 199029 65792

SQLServer:Databases Log Growths CREMS_IBNR_DB 17 65792

SQLServer:Databases Log Shrinks CREMS_IBNR_DB 0 65792

SQLServer:Broker Activation Tasks Started/sec CREMS_IBNR_DB 0 272696576

SQLServer:Broker Activation Tasks Running CREMS_IBNR_DB 0 65792

SQLServer:Broker Activation Tasks Aborted/sec CREMS_IBNR_DB 0 272696576

SQLServer:Broker Activation Task Limit Reached/sec CREMS_IBNR_DB 0 272696576

SQLServer:Broker Activation Task Limit Reached CREMS_IBNR_DB 0 65792

SQLServer:Broker Activation Stored Procedures Invoked/sec CREMS_IBNR_DB 0 272696576

SQLServer:Catalog Metadata Cache Hit Ratio CREMS_IBNR_DB 19002875 537003264

SQLServer:Catalog Metadata Cache Hit Ratio Base CREMS_IBNR_DB 19003073 1073939712

SQLServer:Catalog Metadata Cache Entries Count CREMS_IBNR_DB 65 65792

SQLServer:Catalog Metadata Cache Entries Pinned Count CREMS_IBNR_DB 0 65792[/font]

[font="Courier New"]object_name counter_name instance_name cntr_value cntr_type

---------------------------- ------------------------------ -------------- ----------- -----------

SQLServer:Buffer Manager Page life expectancy 403 65792

SQLServer:Buffer Node Page life expectancy 000 403 65792

SQLServer:Databases Data File(s) Size (KB) CREMS_IBNR_DB 85522112 65792

SQLServer:Databases Log File(s) Size (KB) CREMS_IBNR_DB 9293944 65792

SQLServer:Databases Log File(s) Used Size (KB) CREMS_IBNR_DB 65896 65792

SQLServer:Databases Percent Log Used CREMS_IBNR_DB 0 65792

SQLServer:Databases Active Transactions CREMS_IBNR_DB 1 65792

SQLServer:Databases Transactions/sec CREMS_IBNR_DB 1416 272696576

SQLServer:Databases Repl. Pending Xacts CREMS_IBNR_DB 0 65792

SQLServer:Databases Repl. Trans. Rate CREMS_IBNR_DB 0 272696576

SQLServer:Databases Log Cache Reads/sec CREMS_IBNR_DB 11201644 272696576

SQLServer:Databases Log Cache Hit Ratio CREMS_IBNR_DB 10918669 537003264

SQLServer:Databases Log Cache Hit Ratio Base CREMS_IBNR_DB 11201644 1073939712

SQLServer:Databases Bulk Copy Rows/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases Bulk Copy Throughput/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases Backup/Restore Throughput/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases DBCC Logical Scan Bytes/sec CREMS_IBNR_DB 565248 272696576

SQLServer:Databases Shrink Data Movement Bytes/sec CREMS_IBNR_DB 0 272696576

SQLServer:Databases Log Flushes/sec CREMS_IBNR_DB 419581 272696576

SQLServer:Databases Log Bytes Flushed/sec CREMS_IBNR_DB 25585492992 272696576

SQLServer:Databases Log Flush Waits/sec CREMS_IBNR_DB 1421 272696576

SQLServer:Databases Log Flush Wait Time CREMS_IBNR_DB 0 65792

SQLServer:Databases Log Truncations CREMS_IBNR_DB 199029 65792

SQLServer:Databases Log Growths CREMS_IBNR_DB 17 65792

SQLServer:Databases Log Shrinks CREMS_IBNR_DB 0 65792

SQLServer:Broker Activation Tasks Started/sec CREMS_IBNR_DB 0 272696576

SQLServer:Broker Activation Tasks Running CREMS_IBNR_DB 0 65792

SQLServer:Broker Activation Tasks Aborted/sec CREMS_IBNR_DB 0 272696576

SQLServer:Broker Activation Task Limit Reached/sec CREMS_IBNR_DB 0 272696576

SQLServer:Broker Activation Task Limit Reached CREMS_IBNR_DB 0 65792

SQLServer:Broker Activation Stored Procedures Invoked/sec CREMS_IBNR_DB 0 272696576

SQLServer:Catalog Metadata Cache Hit Ratio CREMS_IBNR_DB 19717055 537003264

SQLServer:Catalog Metadata Cache Hit Ratio Base CREMS_IBNR_DB 19717253 1073939712

SQLServer:Catalog Metadata Cache Entries Count CREMS_IBNR_DB 65 65792

SQLServer:Catalog Metadata Cache Entries Pinned Count CREMS_IBNR_DB 0 65792[/font]

The log files are on primary partition and the data file on a RAID 5 FastT.

Thanks!

Leon

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

leonp SSCommitted Points: 1962 More actions · Answer 1

leonp

SSCommitted

Points: 1962

December 16, 2008 at 7:49 am

#913149

Was there anything I could have done better to have some one reply?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

leonp (12/16/2008)

Was there anything I could have done better to have some one reply?

I'm not quite sure what you're asking.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leonp SSCommitted Points: 1962 More actions · Answer 3

leonp

SSCommitted

Points: 1962

December 16, 2008 at 8:02 am

#913160

There ya go! I was almost beginning to feel only I could see my posts on the forum ..... I was not getting any replies .....

Gail, nothing significant .... I was just hoping someone took a look at my post and told me what they thought about my problem.

Thanks!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

leonp (12/16/2008)

I was just hoping someone took a look at my post and told me what they thought about my problem.

I'm still not getting what you want.

All I can see from your post is that the page life expectancy looks rather low. That may be normal for this app though. The rest of the stats are so system-dependent that there's no way to say if they're good or bad.

Do you want tips on diagnosing memory/disk bottlenecks? Do you want advice on the app? Do you want comments on disk layout? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leonp SSCommitted Points: 1962 More actions · Answer 5

Gail,

I guess I would want to know about all 3 - memory/disk performance analysis, advice on app and the disk layout (per your convenience).

While I understand that these numbers themselves do not build the complete picture of my environment here, but aren't there some measures that have a consistent interpretation like the PLE. If I have a low PLE, isn't that indicative of a memory pressure? Can a low PLE be okay for an app? I understand from microsoft's documentation, that a consistent PLE < 300 is considered a bottleneck.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

leonp (12/16/2008)

Gail,

I guess I would want to know about all 3 - memory/disk performance analysis, advice on app and the disk layout (per your convenience).

Then you're going to have to give a lot more info.

For disk/memory, the important perfmon counters are:

memory: Available MB

memory: Pages/sec

physical disk: avg sec/read (for each disk)

physical disk: avg sec/write (for each disk)

physical disk: avg disk queue length (for each disk) Not really meaningful on a SAN

physical disk: avg % idle time (for each disk)

SQL Server Buffer manager: Buffer cache hit ratio

SQL Server Buffer manager: checkpoint pages\sec

SQL Server Buffer manager: Lazy writes\sec

SQL Server Memory manager: Total Server memory

SQL Server Memory manager: Target Server memory

What are the most common waits that you're seeing in SQL server (query sys.dm_os_wait_stats)

In addition, what's the memory on the server, what's SQL's max memory? 32 bit or 64 bit?

If you want advice on disk layout, you're going to have to explain the disk layout. What physical disks/arrays does the server have, what files are on each, what Raid levels (where applicable)

If I have a low PLE, isn't that indicative of a memory pressure?

Or really inefficient queries (no indexes, bad queries) that cause far more reads than necessary

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

leonp (12/16/2008)

Gail, nothing significant .... I was just hoping someone took a look at my post and told me what they thought about my problem.

Thanks!

I'd be more interested in knowing what the app is actually doing. Having the app write 10k rows at a time 1k wide seems a bit much for the app. First, how long is it taking it to do a 10k set and second, what is the app doing? What is the source of data for the app? A file? Not trying to be a wise guy here... there's a lot of ways to push large amounts and rowcounts of data into SQL server... tell us more about what you're trying to accomplish with the app... we might have a really great alternative or two.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

leonp SSCommitted Points: 1962 More actions · Answer 8

Gail, Jeff,

My apologies for the silence .... kinda juggling between a flurry of issues here. I could gather some information from the scheduled perfmon logs and other documentation .... I will have the outstanding items soon. Meanwhile, here's what I could gather -

*¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢*

memory: Pages/sec 0.1278

physical disk: avg sec/read (for each disk)

E: (Log - Primary Partition) 1.04ms

H: (Index File group - SAN) 5.75ms

F: (1-Data File group- SAN) 7.18ms

F: (2-Data File group- SAN) 0.97ms

F: (3-Data File group- SAN) 5.71ms

F: (4-Data File group- SAN) 0.00ms

F: (5-Data File group- SAN) 0.00ms

physical disk: avg sec/write (for each disk)

E: (Log - Primary Partition) 11.08ms

H: (Index File group - SAN) 2.04ms

F: (1-Data File group- SAN) 10.6ms

F: (2-Data File group- SAN) 1.31ms

F: (3-Data File group- SAN) 11.02ms

F: (4-Data File group- SAN) 0.00ms

F: (5-Data File group- SAN) 0.00ms

physical disk: avg % idle time (for each disk)

E: (Log - Primary Partition) 81.94%

H: (Index File group - SAN) 99.53%

F: (1-Data File group- SAN) 95.49%

F: (2-Data File group- SAN) 99.94%

F: (3-Data File group- SAN) 95.77%

F: (4-Data File group- SAN) 99.99%

F: (5-Data File group- SAN) 99.99%

SQL Server Buffer manager: Buffer cache hit ratio 99.75

SQL Server Memory manager: Total Server memory 1.616GB

Memory on the server = 3.583 GB

SQL's max memory = 3.583 GB - OS overhead

32 bit or 64 bit = 32-bit

If you want advice on disk layout, you're going to have to explain the disk layout. What physical disks/arrays does the server have, what files are on each, what Raid levels (where applicable)

Here's what I understand of our disk configuration -

Logs on E: - Primary Partition 55% free on 18.7 GB disk

Data on F: which is a spanned volume with 5 LUNS underneath - FastT SAN with 49% free on 498GB

Index on H: which is a simple volume - FastT SAN with 89% free on 307GB

All Disks are reported to be RAID 5

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

About the Application

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

1. This is an Informatica workflow from a UNIX server; reading from and writing back to the SQL server on Windows

2. It has 7-8 processing steps for each of which it reads in a table on the database and writes back to another table(s) on the same database

3. Reviews have been performed in the past to see if the target tables out of the APP could be turned to flatfiles on the SQL server which could then be BCPed into the server; however, action deferred owing to the impact of the change

4. At this time, the ODBC vendor for the APP server (DataDirect) has informed of a "improved-performance" release which should speed up things a little bit

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Gail Shaw

SSC Guru

Points: 1004494

December 22, 2008 at 2:32 pm

#916415

I can't see any indications of poor IO performance from those stats.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leonp SSCommitted Points: 1962 More actions · Answer 10

leonp

SSCommitted

Points: 1962

December 22, 2008 at 2:39 pm

#916418

Gila,

Let me get the other statistics too you had asked for .. perhaps there is something in them that could explain the 'suspect' sub-optimal performance. Meanwhile, heres the question again, if these numbers look good, what is with the low PLE - 200.76?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

leonp (12/22/2008)

Meanwhile, heres the question again, if these numbers look good, what is with the low PLE - 200.76?

Good question. The buffer cache hit ratio's high, so most pages SQL looks for are in cache. If I had to take a wild guess (and that's all this is) there could be a small percentage of pages moving in and out of cache very frequently. That may be due to app design. It may not. I may be completely wrong.

Since it's 32 bit, do you have /3GB enabled? If not, SQL's only using 2 GB memory.

Are you actually seeing performance problems? If so, have you isolated the queries that are giving poor performance?

Have you checked for blocking? Frequent or long duration waits?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

noeld SSC Guru Points: 96590 More actions · Answer 12

noeld

SSC Guru

Points: 96590

December 22, 2008 at 2:52 pm

#916423

The real question is: why are you saying that there is write performance problems ?

* Noel

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

leonp (12/22/2008)

1. This is an Informatica workflow from a UNIX server; reading from and writing back to the SQL server on Windows

2. It has 7-8 processing steps for each of which it reads in a table on the database and writes back to another table(s) on the same database

3. Reviews have been performed in the past to see if the target tables out of the APP could be turned to flatfiles on the SQL server which could then be BCPed into the server; however, action deferred owing to the impact of the change

4. At this time, the ODBC vendor for the APP server (DataDirect) has informed of a "improved-performance" release which should speed up things a little bit

Items 1 and 2 are likely the real problem... I'll just bet it's being done one row at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

leonp SSCommitted Points: 1962 More actions · Answer 14

Oh I should have mentioned this in the initial description -

the DB write performance was the professed bottleneck based on the application session logs - Informatica has this session log that provides timing statistics for each thread within the session

1. Reader thread (read from the database)

2. Transformation thread (processing on the Solaris APP server)

3. Writer thread (write to the database)

as total time in secs, idle time and busy %.

Per a general rule of thumb, for a session, the thread with a 100% busy % is the bottleneck. I am seeing a lot of these for this particular workflow from the server.

Thanks again for the responses!