Design Smarter Indexes

A helpful script for investigating indexes, thanks to Brent Ozar Unlimited.

Code Snippet

1. /**********************
2. This is a demo script from http://brentozar.com
3. Scripts provided for testing/demo purposes only.
4. This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
5. http://creativecommons.org/licenses/by-nc-sa/3.0/
6. ***********************/
7.  
8. /**********************
9. 0. Preliminary Check
10. How long has our instance been up?
11. We want to know to contextualize the number of times the index was needed.
12. **********************/
13. select cast(datediff(hh,sqlserver_start_time,GETDATE())/24.0 as numeric(10,1)) as [Days Uptime]
14. from sys.dm_os_sys_info
15.  
16. --What does this mean for missing index data?
18. /**********************
19. 1. Review missing indexes.
20. Let's create a view to look at them.
21. ***********************/
22. if object_id('dbo.MissingIndexes') is null
23.    exec sp_executesql N'create view dbo.MissingIndexes as select ''''as Stub'
24. GO
25.  
26. ALTER VIEW dbo.MissingIndexes
27. AS
28. SELECT    id.statement,
29.         cast(gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans )as int) AS Impact,
30.         cast(gs.avg_total_user_cost as numeric(10,2)) as [Average Total Cost],
31.         cast(gs.avg_user_impact as int) as [% Reduction of Cost],
32.         gs.user_seeks + gs.user_scans as [Missed Opportunities],
33.         id.equality_columns as [Equality Columns],
34.         id.inequality_columns as [Inequality Columns],
35.         id.included_columns as [Included Columns]
36.         FROM sys.dm_db_missing_index_group_stats AS gs
37.         JOIN sys.dm_db_missing_index_groups AS ig ON gs.group_handle = ig.index_group_handle
38.         JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
39. GO
40.  
41. --Check out your view
42. SELECT * FROM dbo.MissingIndexes
43. ORDER BY Impact desc
44.  
45. /**********************
46. 2. Make a list of the columns which look interesting
47. ***********************/
49. /**********************
50. 3. Review indexes already on the table.
51. This will give you a sense of the current 'weight' of indexes for the table.
52. Look at urgent impact values of 1,000,000+
53. Investigate impact values of 20,000+
54. ***********************/
56. SELECT    OBJECT_NAME(ps.object_id) AS object_name,
57.         ps.index_id,
58.         ISNULL(si.name, '(heap)') AS index_name,
59.         CAST(ps.reserved_page_count * 8 / 1024.0 / 1024.0 AS NUMERIC(10, 2)) AS reserved_GB,
60.         ps.row_count,
61.         ps.partition_number,
62.         ps.in_row_reserved_page_count,
63.         ps.lob_reserved_page_count,
64.         ps.row_overflow_reserved_page_count
65. FROM
66. sys.dm_db_partition_stats ps
67. LEFT JOIN sys.indexes AS si ON ps.object_id = si.object_id AND ps.index_id = si.index_id
68. WHERE
69. OBJECT_NAME(ps.object_id) = 'ENTER_DATABASE_NAME_HERE'
70.  
71. --Check for indexes already on the table which may combine with your desired index.
73. --For example, there may be an existing index which you can add key columns or includes to,
74. --That's always better than creating a new index!
75. --What is our table keyed on?
77. exec sp_helpindex [ENTER_DATABASE_NAME_HERE]
79. /**********************
80. 4. Create an index - if needed
81. ***********************/
82.  
83. --What did this do to our missing index recommendations?
84. SELECT *
85. FROM dbo.MissingIndexes
86. ORDER BY Impact desc
88. --Wait for some time to pass...
90. --Queries are running marathons, taking long walks on beaches, and enjoying the sunset.
91. /**********************
92. 5. Review index usage
93. ***********************/
94. SELECT    o.name as [Object Name],
95.         s.index_id as [Index ID],
96.         ps.partition_number as [Partition Num],
97.         i.name as [Index Name],
98.         i.type_desc as [Index Type],
99.         s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read],
100.         s.user_updates [Total Queries Which Wrote],
101.         ps.row_count as [Row Count],
102.         CASE WHEN s.user_updates < 1 THEN 100
103.              ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0
104.         END AS [Reads Per Write]
105. FROM
106. sys.dm_db_index_usage_stats s
107. JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id
108. JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
109. JOIN sys.objects o ON s.object_id = o.object_id
110. JOIN sys.schemas c ON o.schema_id = c.schema_id
111. WHERE
112. s.database_id=db_id()
113. and o.name = 'ENTER_DATABASE_NAME_HERE'
114.  
115. /**********************
116. 6. Do we still have missing indexes?
117. **********************/
119. SELECT *
120. FROM dbo.MissingIndexes
121. ORDER BY Impact desc

 -Source: http://www.brentozar.com/archive/2011/09/kendra-little-explains-how-design-smarter-indexes/


 

Box Fractal written in Javascript

Code Snippet

1. <!DOCTYPE html>
2. <html>
3. <head>
4.     <meta charset="utf-8" />
5.     <meta name="viewport" content="width=device-width" />
6.     <title>Box Fractal</title>
7.     <link href="/Content/site.css" rel="stylesheet"/>
8.  
9.     <script src="/Scripts/modernizr-2.5.3.js"></script>
10.     
11.     <script src="/Scripts/jquery-1.7.1.js"></script>
12.  
13.     
14. </head>
15. <body>
16.     
17.  
18. <h2>Box Fractal</h2>
19.  
20. <canvas id="myCanvas" width="1600px" height="1200px"  style="border:1px solid #d3d3d3;">
21. Your browser does not support the HTML5 canvas tag.
22. </canvas>
23.  
24. <script type="text/javascript">
25.     function Point(X,Y) {
26.         this.X = X;
27.         this.Y = Y;
28.     };
29.  
30.     function resizeFrame() {
31.         var h = $(window).height();
32.         var w = $(window).width();
33.         $("#myCanvas").css('height', h - 100);
34.         $("#myCanvas").css('width', w - 50);
35.     }
36.  
37.     function canvas() {
38.         this.height = $(window).height();
39.         this.width = $(window).width();
40.     }
41.  
42.     $(document).ready(function () {
43.         jQuery.event.add(window, "load", resizeFrame);
44.         jQuery.event.add(window, "resize", resizeFrame);
45.  
46.         var color = "red";
47.         var backgroundColor = "white";
48.         var lineWidth = 1;
49.         var precision = 1;
50.         var third = 0.333333;
51.  
52.         var g = document.getElementById("myCanvas");
53.         var ctx = g.getContext("2d");
54.         width = g.width;
55.         height = g.height;
56.         ctx.fillStyle = color;
57.         ctx.lineWidth = lineWidth;
58.  
59.         //ctx.beginPath();
60.         draw(width, height);
61.         //ctx.stroke();
62.  
63.         function draw(width, height) {
64.             //Draw initial block
65.             var upperLeft = new Point(0, 0);
66.             ctx.fillRect(upperLeft.X, upperLeft.Y, width, height);
67.  
68.             ctx.fillStyle = backgroundColor;
69.             drawBox(upperLeft, width, height);
70.         }
71.  
72.         function drawBox(upperLeft, width, height) {
73.             if (width * third > precision) {
74.                 var top = new Point(upperLeft.X + width * third, upperLeft.Y);
75.                 var left = new Point(upperLeft.X, upperLeft.Y + height * third);
76.                 var right = new Point(upperLeft.X + 2 * width * third, upperLeft.Y + height * third);
77.                 var bottom = new Point(upperLeft.X + width * third, upperLeft.Y + 2 * height * third);
78.  
79.                 //Remove 4 squares
80.                 ctx.fillRect(top.X, top.Y, width * third, height * third);
81.                 ctx.fillRect(left.X, left.Y, width * third, height * third);
82.                 ctx.fillRect(right.X, right.Y, width * third, height * third);
83.                 ctx.fillRect(bottom.X, bottom.Y, width * third, height * third);
84.  
85.  
86.                 //upperleft box
87.                 drawBox(upperLeft, width * third, height * third);
88.                 //upperright box
89.                 drawBox(new Point(upperLeft.X + 2 * width * third, upperLeft.Y), width * third, height * third);
90.                 //middle box
91.                 drawBox(new Point(upperLeft.X + width * third, upperLeft.Y + height * third), width * third, height * third);
92.                 //lowerleft box
93.                 drawBox(new Point(upperLeft.X, upperLeft.Y + 2 * height * third), width * third, height * third);
94.                 //lowerright box
95.                 drawBox(new Point(upperLeft.X + 2 * width * third, upperLeft.Y + 2 * height * third), width * third, height * third);
96.  
97.             }
98.         }
99.     });
100.  
101. </script>
102. </body>
103. </html>

A couple of Linq to Sql Query Examples

Just a quick reference of a couple basic query conversions for Linq to SQL

Code Snippet

21. //inner join
22.             //SELECT [t0].[CountyName]
23.             //FROM [dbo].[County] AS [t0]
24.             //INNER JOIN [dbo].[State] AS [t1] ON [t0].[StateID] = [t1].[StateID]
25.             //WHERE LOWER([t1].[StateName]) = @p0
26.             var counties =  from c in db.Counties
27.                             join st in db.States on c.StateID equals st.StateID
28.                             where st.StateName.ToLower().Equals("florida")
29.                             select c.CountyName;
30.  
31.  
32.             //left join -- use DefaultIfEmpty and new object
33.             //SELECT [t0].[CountyName],
34.             //(CASE WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
35.             //      ELSE CONVERT(NVarChar(50),[t2].[StateName])
36.             //      END) AS [StateName]
37.             //FROM [dbo].[County] AS [t0]
38.             //LEFT OUTER JOIN
39.             //(
40.             //      SELECT 1 AS [test], [t1].[StateID], [t1].[StateName]
41.             //      FROM [dbo].[State] AS [t1]
42.             //) AS [t2] ON [t0].[StateID] = [t2].[StateID]
43.             var counties2 = from c in db.Counties
44.                             join st in db.States on c.StateID equals st.StateID
45.                             into stateCounties
46.                             from co in stateCounties.DefaultIfEmpty()
47.                             select new { CountyName = c.CountyName, StateName = (co == null)? "": co.StateName };
48.  
49.  
50.             //Same table query
51.             //SELECT [t0].[CountyName]
52.             //FROM [dbo].[County] AS [t0],
53.             //     [dbo].[County] AS [t1]
54.             //WHERE ([t0].[CountyName] = [t1].[CountyName]) AND ([t0].[CountyID] <> [t1].[CountyID])
55.             var counties3 = from c1 in db.Counties
56.                             from c2 in db.Counties
57.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
58.                             select new
59.                             {
60.                                 CountyName = c1.CountyName,
61.                             };
62.  
63.  
64.  
65.  
66.             var counties4 = (from c1 in db.Counties
67.                             from c2 in db.Counties
68.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
69.                             group c1 by c1.CountyName into dups
70.                             select new
71.                             {
72.                                 CountyName = dups.Key,
73.                                 Count = dups.Count()
74.                             }
75.                             ).OrderBy(n => n.CountyName);

Some resources I found useful:
http://codesamplez.com/database/linq-to-sql-join-tutorials
http://weblogs.asp.net/rajbk/archive/2010/03/12/joins-in-linq-to-sql.aspx
http://msdn.microsoft.com/en-us/library/bb386913.aspx
   More specifically to joins:
   http://msdn.microsoft.com/en-us/library/bb399397.aspx

Sierpinski's Triangle written in Javascript

Code Snippet

1. <!DOCTYPE html>
2. <html>
3. <head>
4.     <meta charset="utf-8" />
5.     <meta name="viewport" content="width=device-width" />
6.     <title>Sierpinski Triangle</title>
7.     <link href="/Content/site.css" rel="stylesheet"/>
8.  
9.     <script src="/Scripts/modernizr-2.5.3.js"></script>
10.     
11.     <script src="/Scripts/jquery-1.7.1.js"></script>
12.  
13.     
14. </head>
15. <body>
16.     
17. <h2>Sierpinski Triangle</h2>
18. <canvas id="myCanvas" width="1600px" height="1200px"  style="border:1px solid #d3d3d3;">
19. Your browser does not support the HTML5 canvas tag.
20. </canvas>
21.  
22. <script type="text/javascript">
23.  
24.     function Point(X,Y) {
25.         this.X = X;
26.         this.Y = Y;
27.     };
28.  
29.     function resizeFrame() {
30.         var h = $(window).height();
31.         var w = $(window).width();
32.         $("#myCanvas").css('height', h - 100);
33.         $("#myCanvas").css('width', w - 50);
34.     }
35.  
36.     function canvas() {
37.         this.height = $(window).height();
38.         this.width = $(window).width();
39.     }
40.  
41.     $(document).ready(function () {
42.         jQuery.event.add(window, "load", resizeFrame);
43.         jQuery.event.add(window, "resize", resizeFrame);
44.  
45.         var color = "red";
46.         var backgroundColor = "white";
47.         var lineWidth = 1;
48.         var precision = 1; //30,10,8
49.         //var canvasDim = new canvas();
50.  
51.         var g = document.getElementById("myCanvas");
52.         var ctx = g.getContext("2d");
53.         width = g.width;
54.         height = g.height;
55.         ctx.fillStyle = backgroundColor;
56.         ctx.fillRect(0, 0, width, height);
57.         ctx.strokeStyle = color;
58.         ctx.lineWidth = lineWidth;
59.  
60.         ctx.beginPath();
61.         draw(width, height);
62.         ctx.stroke();
63.  
64.  
65.         function drawLine(pt1, pt2) {
66.             ctx.moveTo(pt1.X, pt1.Y);
67.             ctx.lineTo(pt2.X, pt2.Y);
68.             //requestAnimationFrame(render);
69.         }
70.  
71.         function draw(width, height) {
72.             //Draw initial outer triangle
73.             var top = new Point(width >> 1, 0);
74.             var bottomLeft = new Point(0, height);
75.             var bottomRight = new Point(width, height);
76.  
77.             drawLine(top, bottomLeft);
78.             drawLine(bottomLeft, bottomRight);
79.             drawLine(bottomRight, top);
80.  
81.             drawSierp(top, bottomLeft, bottomRight);
82.         }
83.  
84.         function drawSierp(a, b, c) {
85.             if ((a.X - b.X) > precision) {
86.                 drawLine(a, b);
87.                 drawLine(b, c);
88.                 drawLine(c, a);
89.                 drawLine(a, new Point((a.X + b.X) >> 1, (a.Y + b.Y) >> 1));
90.                 drawLine(b, new Point((a.X + b.X) >> 1, (a.Y + b.Y) >> 1));
91.                 drawLine(c, new Point((b.X + c.X) >> 1, (b.Y + c.Y) >> 1));
92.                 drawLine(a, new Point((c.X + a.X) >> 1, (c.Y + a.Y) >> 1));
93.                 drawLine(b, new Point((b.X + c.X) >> 1, (b.Y + c.Y) >> 1));
94.                 drawLine(c, new Point((c.X + a.X) >> 1, (c.Y + a.Y) >> 1));
95.  
96.                 drawSierp(a, new Point((a.X + b.X) >> 1, (a.Y + b.Y) >> 1), new Point((c.X + a.X) >> 1, (c.Y + a.Y) >> 1));
97.                 drawSierp(new Point((a.X + b.X) >> 1, (a.Y + b.Y) >> 1), b, new Point((b.X + c.X) >> 1, (b.Y + c.Y) >> 1));
98.                 drawSierp(new Point((c.X + a.X) >> 1, (c.Y + a.Y) >> 1), new Point((b.X + c.X) >> 1, (b.Y + c.Y) >> 1), c);
99.             }
100.         }
101.  
102.     });
103. </script>
104. </body>
105. </html>