Archive for July 2011

Statistical System functions in TSQL

TSQL has a bunch of statistical system functions that can be used to return information about the system. This includes details about the number of connection attempts, cpu time, and total reads and writes and more. Many of these functions will be useful for performance monitoring. All of these functions return values that indicate cumulative […]

The NTILE TSQL Function

The NTILE is used to assigned records into the desired number of groups. NTILE assigns a number to each record indicating the group it belongs to. The number of records in each group will be the same if possible, otherwise some groups will have less than the others. This function may be useful for assigning […]

Identify the Active Cluster Node with Powershell

I wanted to find a way of programatically identifying the active node of a SQL Server Cluster. I found this post that demonstrated how to do it with TSQL. As I love Powershell so much here’s another method to do it; ?View Code POWERSHELL# Set cluster name $cluster_name = "ClusterName"; # Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") […]

The GROUPING_ID TSQL Function

The GROUPING_ID function computes the level of grouping in a resultset. It can be used in the SELECT, HAVING or ORDER BY clauses when used along with GROUP BY. The expression used in this function must match what has been used in the GROUP BY clause. This function can be usefully deployed to order a […]

Failover All Cluster Resources With Powershell

Here’s a simple example showing how to manage the failover process with Powershell, making sure all resources are running on one node. First, execute the below command to show which node owns the resources. ?View Code POWERSHELLGet-ClusterGroup -Cluster ClusterName | Format-Table -AutoSize; Name OwnerNode State —- ——— —– SoStagSQL20Dtc Node1 Online SQL Server Node1 Online […]