{"id":1828,"date":"2016-02-16T16:30:00","date_gmt":"2016-02-16T14:30:00","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1828"},"modified":"2016-02-14T22:05:10","modified_gmt":"2016-02-14T20:05:10","slug":"qa-from-the-dba-fundamentals-virtual-chapter-presentation","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/02\/16\/qa-from-the-dba-fundamentals-virtual-chapter-presentation\/","title":{"rendered":"Q&A from the DBA Fundamentals Virtual Chapter presentation"},"content":{"rendered":"

A couple weeks ago I presented to the DBA Fundamentals virtual chapter. The presentation was recorded and is available from their site.<\/a><\/p>\n

While I answered some questions during the presentation, I couldn\u2019t answer all of them. Hence this blog post with the rest of the questions and some answers.<\/p>\n

Q1: Is monitoring any different in Azure SQL DB?<\/h3>\n

A1: Completely different. What I was talking about when the question was asked was perfmon counters and wait stats. Since you don\u2019t have access to the server with the SQL DB, you can\u2019t run perfmon. Even if you could, there\u2019s unknown other workloads on the server which would make any such monitoring useless. Instead you can use the DMV sys.dm_db_resource_stats<\/code>, which gives you the resource consumption relative to the maximum allowed for the tier that you\u2019re paying for. For more details, see https:\/\/azure.microsoft.com\/en-us\/blog\/azure-sql-database-introduces-new-near-real-time-performance-metrics\/<\/a><\/p>\n

The wait stats can be monitored with the DMV sys.dm_db_wait_stats, instead of sys.dm_os_wait_stats that you\u2019d use on an earthed SQL Server. See https:\/\/msdn.microsoft.com\/en-us\/library\/dn269834.aspx<\/a><\/p>\n

Q2: What interval should we use for perfmon and how long should it be run?<\/h3>\n

A2: Personally I\u2019m happy using the 15 second default in most cases. Perfmon has minimal overhead and the files aren\u2019t large. If I\u2019m trying to pin down an intermittent issue I\u2019ll reduce the time, but I\u2019ll very rarely increase it.<\/p>\n

When analysing a server, I want minimum a day and that\u2019s bare minimum. A week is good, that way I can see trends over several days and not be caught out by any non-standard workloads on one day.<\/p>\n

Q3: Use performance monitor or sys.dm_os_performance_counters<\/h3>\n

Perfmon. Running a job every 15 seconds is hard and only the SQL counters are available through the DMV, so I\u2019ll just use a performance monitor counter trace and save out as a binary file.<\/p>\n

Q4: Is high CXPacket a problem?<\/h3>\n

By itself, all CXPacket waits mean is that queries are running in parallel. To determine whether that\u2019s a problem or not requires looking at the queries that are running in parallel and seeing whether they should be, or whether they should be serial.<\/p>\n

Most cases I\u2019ve seen recently with very high CXPacket waits and very high Access_Methods_Dataset_Parent latch waits have been a result of inefficient queries and poor indexing, not a problem with parallelism itself.<\/p>\n

http:\/\/sqlperformance.com\/2015\/06\/sql-performance\/knee-jerk-wait-statistics-cxpacket<\/a><\/p>\n

Q5: What should average PLE be?<\/h3>\n

The higher the better. It measures how long, on average, a page stays in cache. Lower numbers mean more churn of the buffer pool. There\u2019s no one number where above is good and below is bad.<\/p>\n

http:\/\/sqlperformance.com\/2014\/10\/sql-performance\/knee-jerk-page-life-expectancy<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

A couple weeks ago I presented to the DBA Fundamentals virtual chapter. The presentation was recorded and is available from their site. While I answered some questions during the presentation, I couldn\u2019t answer all of them. Hence this blog post… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New blog post: Q&A from the DBA Fundamentals Virtual Chapter presentation","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16],"tags":[],"class_list":["post-1828","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-tu","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1828","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1828"}],"version-history":[{"count":2,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1828\/revisions"}],"predecessor-version":[{"id":1830,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1828\/revisions\/1830"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1828"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1828"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1828"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}