Powershell Profile Setup and Resize script on startup

1)Setup your profile for powershell: 

if (!(test-path $profile )) 
{new-item -type file -path $profile -force} 

2)Open up the profile:

Notepad $profile

3)Cut and paste resize into notepad and save: 

##
## Author   : Roman Kuzmin
## Synopsis : Resize console window/buffer using arrow keys
##

function Size($w, $h)
{
    New-Object System.Management.Automation.Host.Size($w, $h)
}

function resize()
{
Write-Host '[Arrows] resize  [Esc] exit ...'
$ErrorActionPreference = 'SilentlyContinue'
for($ui = $Host.UI.RawUI;;) {
    $b = $ui.BufferSize
    $w = $ui.WindowSize
    switch($ui.ReadKey(6).VirtualKeyCode) {
        37 {
            $w = Size ($w.width - 1) $w.height
            $ui.WindowSize = $w
            $ui.BufferSize = Size $w.width $b.height
            break
        }
        39 {
            $w = Size ($w.width + 1) $w.height
            $ui.BufferSize = Size $w.width $b.height
            $ui.WindowSize = $w
            break
        }
        38 {
            $ui.WindowSize = Size $w.width ($w.height - 1)
            break
        }
        40 {
            $w = Size $w.width ($w.height + 1)
            if ($w.height -gt $b.height) {
                $ui.BufferSize = Size $b.width $w.height
            }
            $ui.WindowSize = $w
            break
        }
        27 {
            return
        }
    }
  }
}

4) Change policy for the profile, exit powershell and open a new version "Run as Administrator":

Set-ExecutionPolicy bypass -Scope CurrentUser

5) Close powershell and open a new version, type resize.

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/


 

View overall I/O statistics for all databases in your server

A sql script to find the I/O statistics for all databases on the server. This can be helpful for

"drafting a server consolidation strategy, or pointing to eventual need to check perf counters if I/O is waiting more than expected"

- From "Blog do Ezequiel" http://blogs.msdn.com/b/blogdoezequiel/archive/2012/03/08/the-sql-swiss-army-knife-3-view-i-o-per-file-updated.aspx

Code Snippet

-- 2012-03-08 Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
--
-- Checks the cumulative IO per database file and related information
--
SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
    CAST (CASE
        -- Handle UNC paths (e.g. '\\fileserver\readonlydbs\dept_dw.ndf')
        WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
            THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
            -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
            WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
            THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS NVARCHAR(255)) AS logical_disk,
    fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
    fs.num_of_reads, fs.num_of_writes,
    fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
    fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
    fs.io_stall/1000/60 AS io_stall_min,
    fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
    fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
    ((fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
    ((fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
    ABS((sample_ms/1000)/60/60) AS 'sample_HH',
    ((fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample'
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
ORDER BY 18 DESC

Finding smallest whole number on Google

The Math Factor Podcast was wondering what was the lowest number that Google did not archive.

I figured, a Powershell script would be fun to make to solve this problem. Here is what I wrote so far:

 

clear-variable -name doc
clear-variable -name link
clear-variable -name anchors
clear-variable -name ie
clear-variable -name Notfound

$ie = new-object -com "InternetExplorer.Application"
$Notfound = $true
$i = 1000
$ie.navigate("http://www.google.com/search?hl=en&q=" + $i + "&btnG=Search")
Start-Sleep –s 3

while($Notfound)
{
   $doc = $ie.document

       $anchors = $doc.getElementsByTagName("a")
       "Searching number: " + $i
       $j = $i
       foreach ($link in $anchors)
       {
               if($link.href)
               {
              $link.href
                     if($link.href.StartsWith("http://www.google.com/swr?q="))
                  {
                     $i = $i + 1
                                $ie.navigate("http://www.google.com/search?hl=en&q=" + $i + "&btnG=Search")
                Start-Sleep –s 3
                clear-variable -name doc
                 clear-variable -name link
                clear-variable -name anchors
                  }
               }
       }

        if($j -eq $i)
       {
              $Notfound = $false
              "Number not found: " + $i
       }
}

 

I ran into the problem of my program running to fast, which is why I had to add “Start-Sleep –s 3”. This makes the program wait 3 seconds for google to reply.

The problem with a time delay is, lets assume the number is 1 Million. It would basically take me 34 days to reach the number.

I guess if I has a independent server, I could solve this and let it run for a year.