December 15, 2008 at 2:42 pm
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
December 16, 2008 at 7:49 am
Was there anything I could have done better to have some one reply?
December 16, 2008 at 7:59 am
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
December 16, 2008 at 8:02 am
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!
December 16, 2008 at 8:22 am
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
December 16, 2008 at 8:30 am
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.
December 16, 2008 at 9:33 am
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
December 16, 2008 at 7:56 pm
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
Change is inevitable... Change for the better is not.
December 22, 2008 at 12:51 pm
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/sec0.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
December 22, 2008 at 2:32 pm
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
December 22, 2008 at 2:39 pm
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?
December 22, 2008 at 2:48 pm
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
December 22, 2008 at 2:52 pm
The real question is: why are you saying that there is write performance problems ?
* Noel
December 22, 2008 at 3:21 pm
leonp (12/22/2008)
1. This is an Informatica workflow from a UNIX server; reading from and writing back to the SQL server on Windows2. 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
Change is inevitable... Change for the better is not.
December 22, 2008 at 3:24 pm
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!
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply