Oct 19

Multiline everything matches in VBScript and Javascript

Apparently VBScript uses the same syntax as javascript for regular expressions, so they both suffer from the “. means everything .. except that it doesn’t” bug. You’d think that [.s] would do the trick, but it doesn’t. The best workaround I’ve seen is [sS] (that is, whitespace and not-whitespace).

Oct 18

Maintenance Plan Cleanup Tasks

My observations suggest that you can only add a maintenance cleanup task (to delete expired backups) from the machine on which SQL Server resides. When I run the maintenance plan wizard from SSMS on my own machine, I don’t get that option.

Once you successfully add a maintenance cleanup task, how do you get it to delete .bak, .dif and .trn files all in the same task? Leaving the extension box blank doesn’t result in anything getting deleted. The secret is to enter * as the extension. The period before the extension should be omitted.

Oct 17

Querying Outlook

One of my developers is proposing to put the text of every error email that the web application generates into the database because they contain debug information. I’m not certain that anyone will use this information, so I wanted to estimate how space this was going to take up. I had a bunch of the error emails in my mailbox, and I discovered that if you highlight a bunch of messages, copy, and paste into Excel, you get the same grid. The sizes are strings (“34 KB”), but you can then use a formula to isolate the number (“=INT(LEFT(D2, LEN(D2)-3))”) and use the AVERAGE function on them.

Oct 17

Most Costly Queries Running Right Now

Out of memory? Use the sys.dm_exec_query_memory_grants DMV to find the heaviest query running right now and kill it.

By memory usage:

SELECT session_id, (requested_memory_kb/1024.AS [Requested Memory (MB)], grant_time, query_costtext
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY requested_memory_kb DESC

By query cost:

SELECT session_id, (requested_memory_kb/1024.AS [Requested Memory (MB)], grant_time, query_costtext
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY query_cost DESC

Oct 15

SQL Server 2008 Interesting Features

Extracted from an article on DatabaseJournal.com.

Theoretically, SQL Server 2008 will be released in Q1 of 2008, along with a new version of Visual Studio and Windows.

Data Compression

According to Microsoft, while using compression will slightly increase CPU usage, overall system performance will be improved by reducing I/O and increasing buffer-hit rates. Data and indexes can both be compressed, and it doesn’t affect other features.

Performance Data

More perf counters have been added. The data collected by the counters is stored in a centralized data warehouse. Microsoft states that running the default set of performance related monitors will consume less than 5% of CPU and memory resources.

Performance Dashboard: tool that can read saved performance data. Historical and baseline comparisons can be made and used to create action triggers. For example, if memory use exceeds a threshold for more than five minutes, a more detailed data collection can be automatically triggered.

Performance Studio: a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several collecting methods, including queries, traces, and perf counters. Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options, including suggestions for potential performance tuning.

Installation

The installer will be able to retrieve updates from Microsoft and install SQL Server, service packs, and patches as a single step. Service packs will be uninstallable.

Data Synchronizing Features

SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating disconnected applications which synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.

Dates and Times

New data types in SQL 2008.

  • Date: date only, no time.
  • Time: time only, no date.
  • Date Time Offset: “UTC time-zone aware value” (whatever that means)

Table-Valued Parameters

It’s sort of a hack, but you create a UDT which is a table and pass variables of that type into sprocs.

Merge

A new TSQL command, MERGE, allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.

MERGE InventoryMaster AS im
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON im.InventoryID = src.InventoryID
WHEN MATCHED THEN
UPDATE SET im.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src.InventoryID, src.Descr);