Monthly Archives: February 2009

Find Missing SQL 2005 Indexes

The following script helps you to find the top 10 missing indexes using SQL 2005 DMV.

SELECT TOP 10 migs_adv.index_advantage,
mid.database_id,
mid.object_id,
mid.statement as table_name,
mig.index_handle as index_handle
FROM  (select (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs_adv.group_handle = mig.index_group_handle
AND mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage DESC

Now take the index handle of the top missing index and apply below to determine which columns should be included in the newly created index:

SELECT * FROM sys.dm_db_missing_index_details where index_handle = 45

Once you’ve got your columns, create your new index: (we tend to put indexes in their own fileGroups so they may reside on their own disk spindle for increased performance)

CREATE INDEX missingIndex_xxx
ON [DB].[dbo].[TBL]([col1], [col2], [col3])
INCLUDE ([col4], [col5])
ON FG_XXX_Index;

Advertisements

Find Unused SQL 2005 Indexes

SELECT o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name

The results of this will show you how many times each index has been used, and how often it’s been updated.  If you have 0 seeks, scans, and lookups but a ton of updates, it’s a good bet that the index in question is a waste of time and can be deleted. Conversly, if you have an index that has lots of seeks/scans and minimal updates – it’s really earning it’s paycheck.

The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.

Apple Safari beta 4 now available

Macworld — Apple on Tuesday released a public beta of Safari 4, the next generation of its Internet Web browser.

Apple touts the Nitro engine at the heart of Safari as running JavaScript over 4 times as fast as Safari 3, up to 30 times faster than Internet Explorer 7 and three times faster than Firefox. Nitro also allows Safari 4 to load HTML pages three times faster than IE 7, according to Apple.

Other new features in Safari 4 include Top Sites, giving users a visual preview of frequently visited pages; Full History Search, to search through titles, web addresses and the complete text of recently viewed pages; Cover Flow, to easily flip through web history or bookmarks; and Tabs on Top, to make tabbed browsing easier.

Safari 4 includes HTML 5 support for offline technologies so web-based applications can store information locally without an Internet connection. The browser also supports advanced CSS effects.

Safari 4 public beta is free and available now for download from Apple’s Web site.

Top 100 Twitter Users by Influence

According to Web Analytics Demystified’s Twitter Influence Calculator the following are the 100 most influential members of the Twitter community. For more information about this ranking system please read about the Twitter Influence Calculator or analyze your own influence in Twitter. You may also be interested in the 100 most recently scored Twitter users list.

In the following list, the values for influence, signal to noise ratio are averages of all values in the Twitter Influence Calculator. The values for followers and friends are the highest value contained in the Twitter Influence Calculator. If you have questions about what these terms all mean, please consult the Influence Calculator FAQ.

Rank Username Influence Followers Friends Signal/Noise
1. guykawasaki 72.8 57,240 60,436 58.2%
2. mayhemstudios 70.9 4,607 3,775 45.5%
3. mashable 70.0 51,115 1,697 96.3%
4. BreakingNewsOn 66.9 23,131 11,156 12.0%
5. unmarketing 57.5 7,809 8,329 17.5%
6. barackobama 57.0 223,909 227,134 n/a
7. andysowards 55.7 2,278 1,713 45.1%
8. DaivRawks 54.7 13,982 15,187 33.1%
9. techcrunch 54.6 56,870 564 78.2%
10. chrisbrogan 52.4 38,947 35,109 22.2%
11. leolaporte 51.5 81,280 663 25.0%
12. zaibatsu 51.2 23,976 25,069 43.3%
13. scobleizer 51.1 53,622 56,228 51.4%
14. stephenfry 50.8 100,014 32,173 15.4%
15. perrybelcher 49.3 24,968 26,071 36.3%
16. zappos 47.0 44,195 48,738 59.8%
17. twitter 46.6 67,252 30 50.0%
18. davewiner 45.5 16,685 799 56.7%
19. wilw 44.7 51,215 96 19.8%
20. pistachio 44.1 16,537 18,293 53.5%
21. Jasoncalacanis 43.9 55,459 64,904 52.0%
22. alohaarleen 42.5 31,300 32,530 12.9%
23. timoreilly 42.4 28,696 444 82.0%
24. garyvee 42.2 33,083 4,095 45.0%
25. ijustine 42.0 48,545 1,929 28.6%
26. marismith 41.4 13,878 15,257 48.9%
27. imjustcreative 41.2 5,161 2,594 49.2%
28. ninjen 40.9 539 375 18.7%
29. mrken777 39.1 6,244 6,701 46.9%
30. jowyang 39.1 24,312 6,538 55.1%
31. styletime 39.0 5,572 4,170 47.0%
32. WarrenWhitlock 38.4 12,673 11,196 31.1%
33. JesseNewhart 38.3 10,191 10,369 66.3%
34. lizstrauss 38.1 12,222 10,531 9.6%
35. grader 38.0 30,520 34,227 26.2%
36. starbucks 37.6 38,641 40,317 34.1%
37. lancearmstrong 37.3 53,571 43 24.6%
38. chrispirillo 37.2 28,391 28,032 67.1%
39. brentspiner 36.6 13,702 2 15.3%
40. PRsarahevans 35.4 11,512 5,654 45.6%
41. schofe 35.4 19,993 368 6.2%
42. davejmatthews 35.3 21,596 3 0.0%
43. loic 34.5 19,152 20,403 44.5%
44. stephenkruiser 34.2 10,347 10,796 19.6%
45. mrskutcher 34.1 9,774 23 18.1%
46. flwbooks 34.1 11,174 13,478 44.6%
47. DuncanBannatyne 33.9 1,994 49 4.0%
48. wossy 33.9 13,933 224 0.9%
49. petersantilli 33.4 11,079 11,236 21.5%
50. sweetnote 33.0 1,924 2,000 27.5%
51. AMOYal 32.2 1,460 1,595 15.1%
52. chasrmartin 32.0 793 1,004 26.2%
53. opieradio 31.8 5,895 16 38.7%
54. cheeky_geeky 31.8 3,797 357 60.6%
55. dannybrown 31.6 4,478 4,395 28.6%
56. deannatroupe 31.5 1,769 1,769 23.4%
57. AaronMartirano 30.2 6,953 7,335 38.5%
58. sethsimonds 30.0 11,405 11,467 12.0%
59. cathrynmarie 29.9 308 316 11.2%
60. kellyolexa 29.8 2,958 2,683 3.8%
61. tygerbaby 29.6 729 760 6.5%
62. missrogue 29.6 17,088 1,166 23.2%
63. askbillmitchell 29.2 1,564 2,012 6.3%
64. armano 28.9 10,379 3,000 43.7%
65. skydiver 28.1 23,656 610 55.6%
66. ev 28.0 53,113 919 16.7%
67. tommytrc 28.0 1,328 1,488 9.1%
68. robmcnealy 28.0 23,004 23,680 17.5%
69. mediaphyter 27.0 6,055 811 22.3%
70. shelisrael 26.0 11,058 1,670 34.6%
71. ambercadabra 25.7 5,754 5,331 7.8%
72. donttrythis 25.6 5,869 23 22.1%
73. melissatweets 25.6 2,598 2,720 32.0%
74. Twitter_Tips 25.4 9,996 9,599 99.4%
75. cutiebootycakes 25.1 3,089 2,918 13.1%
76. jerell 25.1 16,267 17,894 14.8%
77. slugger41 25.1 906 1,083 12.7%
78. AndrewWindham 25.0 12,054 11,872 19.4%
79. joeschmitt 24.9 2,130 838 51.3%
80. mchammer 24.7 31,425 20,353 14.5%
81. wbaustin 24.7 20,341 22,484 78.0%
82. vojha 24.6 163 155 16.4%
83. hodgman 24.6 31,183 88 25.9%
84. michellemalkin 24.0 4,219 358 65.4%
85. queenofspain 23.8 7,975 7,929 22.2%
86. shanselman 23.6 6,565 481 52.3%
87. steverubel 23.5 16,388 506 76.2%
88. caseywright 23.3 20,598 19,921 67.5%
89. huffpost 23.0 4,241 159 78.5%
90. Minervity 22.9 533 533 48.8%
91. gruber 22.8 20,266 265 44.8%
92. howardlindzon 22.8 3,782 251 25.0%
93. RightGirl 22.7 879 505 26.3%
94. TwitCalc 22.5 155 0 99.7%
95. cameronolivier 22.2 434 485 41.0%
96. briancray 22.0 653 588 50.9%
97. codinghorror 22.0 8,572 42 43.4%
98. sashakane 21.8 4,753 4,957 18.0%
99. courosa 21.8 2,103 997 24.1%
100. documentally 21.7 2,532 1,737 33.3%

The previous list is based on all Twitter users analyzed by Web Analystics on February 3, 2009.

Lastly, you can follow me here: http://www.twitter.com/stevenRobert