Davide Mauri : DMVhttp://sqlblog.com/blogs/davide_mauri/archive/tags/DMV/default.aspxTags: DMVenCommunityServer 2.1 SP2 (Build: 61129.1)SSAS DMVs: useful linkshttp://sqlblog.com/blogs/davide_mauri/archive/2012/05/18/ssas-dmvs-useful-links.aspxFri, 18 May 2012 14:47:42 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:43450Davide Mauri0http://sqlblog.com/blogs/davide_mauri/comments/43450.aspxhttp://sqlblog.com/blogs/davide_mauri/commentrss.aspx?PostID=43450<p>From time to time happens that I need to extract metadata informations from Analysis Services DMVS in order to quickly get an overview of the entire situation and/or drill down to detail level. As a memo I post the link I use most when need to get documentation on SSAS Objects Data DMVs:</p> <p>SSAS: Using DMV Queries to get Cube Metadata <br /><a title="http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/" href="http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/">http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/</a></p> <p>SSAS DMV (Dynamic Management View) <br /><a title="http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/" href="http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/">http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/</a></p> <p>Use Dynamic Management Views (DMVs) to Monitor Analysis Services <br /><a title="http://msdn.microsoft.com/en-us/library/hh230820.aspx" href="http://msdn.microsoft.com/en-us/library/hh230820.aspx">http://msdn.microsoft.com/en-us/library/hh230820.aspx</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=43450" width="1" height="1">20082008 R22012Analysis ServicesDMVSYS2 scripts updated (December 2012)http://sqlblog.com/blogs/davide_mauri/archive/2011/12/13/sys2-scripts-updated-december-2012.aspxMon, 12 Dec 2011 22:26:43 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:40288Davide Mauri0http://sqlblog.com/blogs/davide_mauri/comments/40288.aspxhttp://sqlblog.com/blogs/davide_mauri/commentrss.aspx?PostID=40288<p>I’ve updated my SYS2 scripts:</p> <ul> <li>Added a new script to see how much buffer cache memory is used by each database </li> <li>Updated the sys2.stats script in order to have only one row per statistics </li> <li>Updated the sys2.query_stats script to use the sys.dm_exec_plan_attributes dmv to get better information on which database was used by the cached plans </li> </ul> <p>As usual they are available from CodePlex:</p> <p><a title="http://sys2dmvs.codeplex.com/" href="http://sys2dmvs.codeplex.com/">http://sys2dmvs.codeplex.com/</a></p> <p>Enjoy!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=40288" width="1" height="1">200520082008 R2DMVsys2SYS2 Scripts Updated – Scripts to monitor database backup, database space usage and memory grants now availablehttp://sqlblog.com/blogs/davide_mauri/archive/2011/02/13/sys2-scripts-updated-scripts-to-monitor-database-backup-database-space-usage-and-memory-grants-now-available.aspxSun, 13 Feb 2011 17:30:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:33451Davide Mauri2http://sqlblog.com/blogs/davide_mauri/comments/33451.aspxhttp://sqlblog.com/blogs/davide_mauri/commentrss.aspx?PostID=33451<p>I’ve just released three new scripts of my “sys2” script collection that can be found on CodePlex:</p> <p>Project Page: <a title="http://sys2dmvs.codeplex.com/" href="http://sys2dmvs.codeplex.com/">http://sys2dmvs.codeplex.com/</a></p> <p>Source Code Download: <a title="http://sys2dmvs.codeplex.com/SourceControl/changeset/view/57732" href="http://sys2dmvs.codeplex.com/SourceControl/changeset/view/57732">http://sys2dmvs.codeplex.com/SourceControl/changeset/view/57732</a></p> <p>The three new scripts are the following</p> <ul> <li>sys2.database_backup_info.sql </li> <li>sys2.query_memory_grants.sql </li> <li>sys2.stp_get_databases_space_used_info.sql </li> </ul> <p>Here’s some more details:</p> <blockquote> <p><strong>database_backup_info</strong></p> <p>This script has been made to quickly check if and when backup was done. It will report the last full, differential and log backup date and time for each database. Along with these information you’ll also get some additional metadata that shows if a database is a read-only database and its recovery model:</p> <p><a href="http://sqlblog.com/blogs/davide_mauri/image_4B56D20D.png"><img style="background-image:none;border-right-width:0px;padding-left:0px;padding-right:0px;display:inline;border-top-width:0px;border-bottom-width:0px;border-left-width:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/davide_mauri/image_thumb_269594C9.png" width="1028" height="109" /></a></p> <p>By default it will check only the last seven days, but you can change this value just specifying how many days back you want to check.</p> <p>To analyze the last seven days, and list only the database with FULL recovery model without a log backup</p> <p><font face="Courier New">select * from sys2.databases_backup_info(default) <br />where recovery_model = 3 and log_backup = 0</font></p> <p>To analyze the last fifteen days, and list only the database with FULL recovery model with a differential backup</p> <p><font face="Courier New">select * from sys2.databases_backup_info(15) <br />where recovery_model = 3 and diff_backup = 1</font></p> <p>I just love this script, I use it every time I need to check that backups are not too old and that t-log backup are correctly scheduled.</p> <p><strong>query_memory_grants</strong></p> <p>This is just a wrapper around sys.dm_exec_query_memory_grants that enriches the default result set with the text of the query for which memory has been granted or is waiting for a memory grant and, optionally, its execution plan</p> <p><strong>stp_get_databases_space_used_info</strong></p> <p>This is a stored procedure that list all the available databases and for each one the overall size, the used space within that size, the maximum size it may reach and the auto grow options. This is another script I use every day in order to be able to monitor, track and forecast database space usage.</p> </blockquote> <p>As usual feedbacks and suggestions are more than welcome!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=33451" width="1" height="1">200520082008 R2DMVScriptssys2T-SQLViewing how much memory is used by not reused query planhttp://sqlblog.com/blogs/davide_mauri/archive/2010/07/23/viewing-how-much-memory-is-used-by-not-reused-query-plan.aspxFri, 23 Jul 2010 08:48:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:27251Davide Mauri9http://sqlblog.com/blogs/davide_mauri/comments/27251.aspxhttp://sqlblog.com/blogs/davide_mauri/commentrss.aspx?PostID=27251<p>One interesting observation that may help developers get convinced that they should parametrize query and that they must check that the ORM they use does it correctly is show how much memory can be wasted by plans that cannot be effectively reused.</p> <p>The following query can help on this:</p> <p><font size="2" face="Courier New">with cte as ( <br />&#160;&#160;&#160; select <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; reused = case when usecounts &gt; 1 then 'reused_plan_mb' else 'not_reused_plan_mb' end, <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; size_in_bytes, <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; cacheobjtype, <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; objtype <br />&#160;&#160;&#160; from <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; sys.dm_exec_cached_plans <br />), cte2 as <br />( <br />&#160;&#160;&#160; select <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; reused, <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; objtype, <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; cacheobjtype, <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; size_in_mb = sum(size_in_bytes / 1024. / 1024.) <br />&#160;&#160;&#160; from <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; cte <br />&#160;&#160;&#160; group by <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; reused, cacheobjtype, objtype <br />), cte3 as <br />( <br />&#160;&#160;&#160; select <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; * <br />&#160;&#160;&#160; from <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; cte2 c <br />&#160;&#160;&#160; pivot <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( sum(size_in_mb) for reused in ([reused_plan_mb], [not_reused_plan_mb])) p <br />) <br />select <br />&#160;&#160;&#160; objtype, cacheobjtype, [reused_plan_mb] = sum([reused_plan_mb]), [not_reused_plan_mb] = sum([not_reused_plan_mb]) <br />from <br />&#160;&#160;&#160; cte3 <br />group by <br />&#160;&#160;&#160; objtype, cacheobjtype <br />with rollup <br />having <br />&#160;&#160;&#160; (objtype is null and cacheobjtype is null) or (objtype is not null and cacheobjtype is not null)</font></p> <p>The result is something like this:</p> <p><img style="border-bottom:0px;border-left:0px;display:inline;border-top:0px;border-right:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/davide_mauri/image_578A81C8.png" width="418" height="233" /> </p> <p>as you can see this server (SQL Server 2005 SP2) is using near 1.8GB of memory for Plan Caching and one third is memory that contains plans that are never reused (column usecount = 1 as reported by DMV <em>sys.dm_exec_cached_plans</em>)</p> <p></p> <p>Monitoring the result of this query can also show how much impact the usage of the new (SQL Server 2008) “<a href="http://msdn.microsoft.com/en-us/library/cc645587.aspx">Optimize for ad-hoc workload</a>” option can have on a system.</p> <p>PS</p> <p>I’ve also updated my <em>sys2dmv</em> project on CodePlex with this new script:</p> <p><a title="http://sys2dmvs.codeplex.com/" href="http://sys2dmvs.codeplex.com/">http://sys2dmvs.codeplex.com/</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=27251" width="1" height="1">CachingDMVScriptssys2SYS2 DMVs on CodePlexhttp://sqlblog.com/blogs/davide_mauri/archive/2010/01/18/sys2-dmvs-on-codeplex.aspxMon, 18 Jan 2010 18:09:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21152Davide Mauri0http://sqlblog.com/blogs/davide_mauri/comments/21152.aspxhttp://sqlblog.com/blogs/davide_mauri/commentrss.aspx?PostID=21152<p>I’ve just created a new project on CodePlex anmed <em>SYS2DMVS</em>, where I’ll put all my <em><a href="http://www.davidemauri.it/resources/sql-scripts.aspx" target="_blank">sys2 scripts</a></em> so that they can be found and accessed more easily. Also it will more easy for me to maintain them, just a Check-In with SVN and that’s it :-).</p> <p><a title="http://sys2dmvs.codeplex.com/" href="http://sys2dmvs.codeplex.com/">http://sys2dmvs.codeplex.com/</a></p> <p>The scripts are growing in number, they are now 9, so I though that they deserve a better place to stay than my personal website.</p> <p>This also has another important meaning: if you want to contribuite, I’ll be *very* *very* happy to have you on board for this project. I think it would be very nice and useful if we can make this project grow all togheter.</p> <p>So, if you have a script to share, and you have the will to make it adhere to a certain style (I followed the DMVs style: lower case name, using “_” to separate names, and so on) and to share your own scripts with the community, you’re more than welcome!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=21152" width="1" height="1">20052008DMVScriptssys2T-SQLsys2 scripts updatedhttp://sqlblog.com/blogs/davide_mauri/archive/2010/01/07/sys2-scripts-updated.aspxThu, 07 Jan 2010 20:54:10 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:20694Davide Mauri0http://sqlblog.com/blogs/davide_mauri/comments/20694.aspxhttp://sqlblog.com/blogs/davide_mauri/commentrss.aspx?PostID=20694<p>I’ve updated my “sys2” scripts with three 3 new scripts:</p> <p><strong><em>sys2.objects_dependencies</em></strong> <br />A wrapper around sys.sql_expression_dependencies that shows also related informations taken from sys.object table, like object name, object type and schema name of the referencing entity. </p> <p><strong><em>sys2.objects_partition_ranges</em></strong> <br />Shows information on partitioned table like rows per partition, partition ranges and partition filegroup destination. </p> <p><strong><em>sys2.objects_data_spaces</em></strong> <br />List tables and indexes and show in which filegroup they reside. </p> <p>They are freely available – along with all the others – here:</p> <p><a title="http://www.davidemauri.it/resources/sql-scripts.aspx" href="http://www.davidemauri.it/resources/sql-scripts.aspx">http://www.davidemauri.it/resources/sql-scripts.aspx</a></p> <p>Enjoy!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=20694" width="1" height="1">20052008DMVObject DependenciesPartitioningScriptsSQL Serversys2T-SQL