tag:blogger.com,1999:blog-16771259.post2966510919840594252..comments2024-03-07T03:02:45.934-08:00Comments on SQL Server Code,Tips and Tricks, Performance Tuning: Use T-SQL to create caveman graphsUnknownnoreply@blogger.comBlogger5125tag:blogger.com,1999:blog-16771259.post-87798229004402917192017-12-13T10:51:23.465-08:002017-12-13T10:51:23.465-08:00sum over version
select
db_name(mf.database_i...sum over version<br /><br />select<br /> db_name(mf.database_id) database_name<br /> , sum(mf.size) * 8. / 1024 / 1024 total_size_GB<br /> , convert(<br /> numeric(19, 2)<br /> , sum(mf.size) * 100.0<br /> / sum(sum(mf.size)) over (partition by (select null))<br /> ) percent_size<br /> , replicate(<br /> '|'<br /> , sum(mf.size) * 100.0<br /> / sum(sum(mf.size)) over (partition by (select null))<br /> ) graph<br />from<br /> sys.master_files mf<br />group by<br /> mf.database_id<br />order by<br /> percent_size descUnknownhttps://www.blogger.com/profile/08150847011734432043noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-31881810425366563652017-12-11T19:19:56.492-08:002017-12-11T19:19:56.492-08:00Thank you ! I tried to compare the actual executi...Thank you ! I tried to compare the actual execution plans. I also looked at statistics IO for both cases, Same, I think. Curious why, but will look more Anonymoushttps://www.blogger.com/profile/10234041890643824425noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-49308655867149556722017-12-11T19:03:17.689-08:002017-12-11T19:03:17.689-08:00Wonderful ! Now,,, if only I can add in some colo...Wonderful ! Now,,, if only I can add in some colors ... Anonymoushttps://www.blogger.com/profile/10234041890643824425noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-14765154644585585522017-12-11T02:31:38.314-08:002017-12-11T02:31:38.314-08:00Simplistic and easy to adapt. Cool. Thanks for the...Simplistic and easy to adapt. Cool. Thanks for the post.hot2usehttps://dba.stackexchange.com/users/15356/hot2use?tab=profilenoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-58739472713219700942017-12-10T20:06:13.184-08:002017-12-10T20:06:13.184-08:00Good. Instead of referencing same table sys.maste...Good. Instead of referencing same table sys.master_files thrice , cte or <br />sum over() can be used <br /><br />;with dbsize_CTE as <br />(<br /> select DBName = DB_NAME(database_id),<br /> CAST(SUM(size) * 8. / 1024/1024 AS DECIMAL(30,2)) as dbsize<br /> from sys.master_files WITH(NOWAIT)<br /> group by database_id<br />) ,<br /><br />totsize_CTE as <br />(<br /> select sum(dbsize) as totsize <br /> from dbsize_CTE<br />) <br /><br />select d.DBNAME,D.dbsize, percent_size = (D.dbsize/T.totsize) * 100.00 ,<br />replicate ('|',(D.dbsize/T.totsize) * 100.00) as GraphPercent<br />from dbsize_CTE as D,<br />totsize_CTE as T <br />order by percent_size descSrinihttps://www.blogger.com/profile/13510884022807107082noreply@blogger.com