Special thanks to Kimberly L. Tripp for this query
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs – USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
Full article: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx#ixzz00P7SU8xp
Quick Tip from a respected MS Instructor: Disable SQL Server Browser Service
This causes a LOT of wasted network traffic and opens up a vulnerability making it easier for hackers to find your data servers.
Turn this off and advise your users to explicity enter the server name and not use the “browse” feature.
SQLActiveScriptHost.Print “Hello World”
Dim conn, rs
Dim sql, sProvider, sCString
”sProvider = “Microsoft.JET.OLEDB.4.0”
sProvider = “Microsoft.ACE.OLEDB.12.0”
sCString = “Data Source= c:temptryme.mdb”
sql = “SELECT * FROM tryme;”
Set conn = SQLActiveScriptHost.CreateObject(“ADODB.Connection”)
.Provider = sProvider
.Mode = adModeRead
.ConnectionString = sCString
Set rs = conn.Execute(sql)
While Not rs.EOF
SQLActiveScriptHost.Print “Executed SQL…”
Set conn = Nothing
SQLActiveScriptHost.Print “Connection Closed.”
Note: SQLActiveScriptHost is being deprecated in a future version of SQL Server (Post 2008 R2)
$adOpenStatic = 3
$adLockOptimistic = 3
$adStatusOpen = 1
$sql = "Select * from dispatch;"
$cstr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:tempmydata.mdb; Jet OLEDB:Database Password=mypass;"
$conn = New-Object -comobject ADODB.Connection
$rs = New-Object -comobject ADODB.Recordset
if ($conn.State -eq $adStatusOpen)
$rs.Open($sql, $conn, $adOpenStatic, $adLockOptimistic)
if ($rs.State -eq $adStatusOpen)
30 January 2012
by Rob Garrison
* Where appropriate, use VARCHAR(n) over VARCHAR(MAX) for reasons of good design if not performance benefits, and because VARCHAR(MAX) data does not compress
* Storing large strings takes longer than storing small strings.
* Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.
* Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.
* Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.
Ran into an interesting problem with a SQL2005 database being restored to a SQL2008R2 instance.
Turned out the database owner was correctly represented as the login that restored the database, however the login mapped to the “dbo” user was still mapped to the old login on the source server.
To fix this, ran the following:
alter authorization on database::[database_name] to [login_name]