CPU and Scheduler Performance Monitoring using SQL Server and Excel

Derek Colley from SQLServerCentral.com

This article will demonstrate a method of creating an Excel-based CPU/scheduler performance dashboard for SQL Server 2005+. More »

SQLServerCentral Webinar Series #21 - Forgotten Rings & Other Monitoring Stories

Press Release from Red-Gate

Most common monitoring metrics are important and useful, especially over time, but they can fall short. How do you gather information to determine, for example, if you have buffer cache pressure? Register now for the free webinar. Wednesday, October 17 2012 4:00pm - 5:00pm BST More »

Matrix Math in SQL

Additional Articles from SimpleTalk

Relational Datbases have tables as data structures, not arrays. This makes it tricky and slow to do matrix operations, but it doesn't mean it is impossible to do. Joe gives the Celko Slant on how to go about doing Matrix Math in SQL. More »

This editorial was originally published on Oct 23, 2007. It is being re-published as Steve is out on the SQL in the City US 2012 tour.

One of the things that's often come up in my career in IT is do we build something or buy it? I'm speaking of software here, if we're in the server, generator, or UPS discussions, I'm definitely in the "buy" camp. I have actually changed the batteries for a 10KVA UPS, which was not fun. Lots and lots of car-sized batteries (not scary) and thick cables (very scary).

However for software, I tend to be in the "build" camp quite often, at least for internal company software. In many of my jobs we've resisted buying too much software, especially anywhere that customization was needed. Since we often spent significant amounts of time making changes, it didn't seem worth it to buy software, especially for the long term. That's for customized packages. I certainly don't advocate writing your own version of Word, Excel, or Outlook.

At SQLServerCentral.com, we recently re-built the site. For the most part.

We bought two significant pieces of software to integrate into the site: an editor, and the forum software. For the editor, I could see that it didn't make much sense to write one. There are many choices and it's a relatively static black box of software, with well defined inputs and outputs, not a lot of interaction points between the editor and the rest of the site.

For the forums, I'm not so sure this was the best choice. Given the time and $$ restrictions, I felt that it made sense to buy it, but it's not been an easy integration. From the software side, I don't think it was too difficult, but from the user experience point of view, it hasn't been great. There are lots of things that don't work well, from the recent posts issues to the notifications, we've had to constantly decide whether we wanted to alter the code or just force the user experience to fit the software.

It's easy to modify the software, but if we get upgrades and patches from the forum vendor, which are inevitable with such a large piece of software, we don't want to have undertake a huge project just to patch the forums. As it is, there's 4 or 5 places that we'll have to potentially rewrite our changes to perform an upgrade.

I've gone through similar debates at other companies. I worked at a small company years ago and we debated about buying an ERP package right after I started. We'd been working with a homegrown, FoxPro for DOS application and I was in favor of buying something. We debated and I ended up on a three year rewrite in Visual FoxPro and SQL Server. It turned out fantastic and later on I argued more against buying something after seeing all the customizations they wanted in our own software. I heard a couple years ago they were looking to buy an ERP package instead of undertaking another rewrite. While I certainly understand their concerns over being in the software business and keeping developers on staff, I also felt their homegrown package gave them a competitive advantage.

And that's the key. If your software can give you a competitive advantage, because you're building something to support your own business, then I think you should write it. Or at least large portions of it. Off the shelf software often doesn't meet your needs and it certainly doesn't give you a competitive advantage, unless you're willing to spend a lot of resources to customize it.

And if you're customizing, you've got a staff of developers. I might make more sense to use them, let them be creative, and build something that can help you succeed.

Steve Jones

Podcast: The Voice of the DBA

Today's podcast has a guest introduction, so check it out. All the shows are at sqlservercentral.podshow.com.

• The Build Buy Debate - Video, Quicktime, 6.5MB
• The Build Buy Debate - Audio, MP3, 6.7MB

As always, feedback for the show is appreciated. Let me (or the boss) know if you like the show and send in comments, suggestions, or ideas to make things better.

The music for today's podcast by Everyday Jones, a great acoustic duo I stumbled on. You can hear more of her music at www.everydayjones.com.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

In full text search, the stemmer for words does what?

By Steve Jones

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: full text search. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

When I execute the following T-SQL

SET ANSI_PADDING ON

DECLARE @a VARCHAR ='xyz'    

SELECT 
  LEN(@a) AS 'Declared'
, LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted'
, LEN(CAST('xyz' AS VARCHAR)) AS 'Cast'

What values are returned (returned values are in Declared, Converted and Cast sequence). For example: 4, 5, 6

By bitbucket

Answer: 1,3,3

Explanation: When n (Where n defines the string length) is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Ref: http://msdn.microsoft.com/en-us/library/ms176089.aspx

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate. 

Counting distinct periods of consecutive days

Harsha Majety from SQLServerCentral.com

Count sequential days as 1 unit More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Indexes question - Hello everyone, In our datawarehousing environment, every morning indexes are dropped and then data is loaded into the database and finally...

DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... The boss and I are conviced that we need a dba since the 3rd party firm providing...

SQL Server 2005 : Backups

Can't perform a full backup due to Full Text-search catalog being offline - I had to attach database to a different server and while it was attaching, I answered NO to turning Full...

SQL Server 2005 : Business Intelligence

Error in precedence Constraint Expression - Hi, Am new to ssis i given the below condition in constraint expression it show error help any body convert(date,getdate(),111)=convert(date,dateadd(dd,-1,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,7-datepart(day,DATEADD(qq, DATEDIFF(qq,0,getdate()),...

Business Intelligence SAS providers - I have Microsoft Visual Studio 2010 installed on SQL Server 2012. I am using SAS dataset as source to SSIS package.But...

SSIS Pakage Error - Executing SSIS package i am getting following error. [Execute SQL Task] Error: Executing the query "exec proc_BusinessRulesValidation @UserName = ?, @..." failed with the...

Error in SSIS package after deployment - Hi I have deployed the package and when i was running this package from other machine i was getting the...

SQL Server 2005 : Data Corruption

Cannot retrieve row from page (1:2108289) by RID -  MigrationData is my production database last few days i got the error message when i execute a procedure on this...

SQL Server 2005 : Development

Removing trailing spaces in a table column - Hi I am having trouble removing trailing spaces in a table column (query phrase). so far i have tried using Ltrim...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL MSDTC Error - Dear Guru's, I am getting following error in my PRODUCTION server. Please note that I do not have Cluster Environment. ERROR:- The description...

SQL Server 2005 64 bit vs 32 bit - Hello to all. I have installed SQL Server 2005 Developer Edition and Visual Studio 2005 Standard Edition on my Windows...

Cannot Create Full Text Index - For some reason, I cannot create a full text index or catalog using SLQ Server 2005. I mark the database...

SQL Server 2005 : SQL Server 2005 Security

Error logging in...The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception - Hello - I have a SQL Server instance that I am only able to get to from my local installation of...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Temp table for Performance Impact - Hi, We are using the table variables inside the sp. It is working fine at QA and staging server. When It...

SQL Server 2005 : SQL Server 2005 Integration Services

Import Multiple XML files into Sql Server using SSIS - Hello All... I have a set of XML files which I need to import into database on Regular basis(after validating some...

SSIS - ForEach From Variable Enumerator - Hi, I'm using ForEach Loop container and selected "ForEach From Variable Enumerator" option. I created a variable of System.Object(User::Collection) and selected...

SSIS - Truncation Warnings - I have created a package to import data into a table that has been created per a data dictionary. I...

SQL Server 2005 : T-SQL (SS2K5)

how to avoid duplicate business logic - Take the following example: [code="sql"]select * from tbl_Users U where U.active = 1 and U.deleted = 0[/code] It seems that the application owner keeps changing...

Performance issue with tally solution - Hello everybody! I have a performance issue with a set based solution. Please could you advice me what I'm doing wrong? I...

SQL Server 7,2000 : Administration

Reduce INITIAL SIZE of TEMPDB - I accidently made the INITIAL SIZE of my TEMPDB much too large. Now when I try to decrease the size,...

SQL Server 7,2000 : Backups

Auto backup and restore in remote SQL server 2000 - Hi Experts, Say I have two Servers (ServerA and ServerB), I have performed database backup from ServerA and Put the backup file...

SQL Server 7,2000 : General

SQL DTS - "Invalid Pointer" - Hi All, I have two SQL servers, one is running SQLServer 7.0 (7.00.842) and the other is running SQLServer 2000 (8.00.760).  I've...

SQL Server 7,2000 : T-SQL

SUBSTRING And CHARINDEX to find start/end of string - Hi all, I have a big long text string that can vary in lengh and contrain audit information about data that...

SQL Server 7,2000 : SQL Server Agent

Please Help Solve Why SSIS Package Fails When Run as SQL Agent Job - We have SQL Server 2005 x86 locally. On it we have a SQL Agent Job that runs an SSIS package...

SQL Server 2008 : SQL Server 2008 - General

Login Access levels - How to get a list of users or logins, their role, and enabled or not. SELECT MemPrin.name AS MemberName, RolPrin.name AS...

Which Option is good - Hi All, I would like to know which tool is good for deployment from one location to another. I need...

Need help in ssis transactions - Hi all, In my package i have one sequence container,in that i have 3 data flow task ...each one access...

Query Performance - Advice needed on Joining same table multiple times - We have a product table which some of the products are kits which include other products. So i have 2...

Isolation Level and LOCK_TIMEOUT - We have a proc that causes high blocking on inserts. The proc joins 3 tables (one of them is where...

determine which transactions add up to a specific value? - I swear I've done this before, but I'm drawing a blank today. in this example, one or more detail transactions should...

Database change policy and procedure - Presently we have no defined process or policy for changes to SQL Server instances and databases in our environment. I'm...

Can't run SSIS package from sqlagent jobs - Hello,When I run package from integration it works,but I am trying to run SSIS package from sqlagent jobs and get...

Active flag on IP Addresses flag of TCP/IP Properties in Configuration Manager - I have two instances on a server. I'm looking in SQL Server Configuration Manager, under the TCP/IP properties for each...

Easy Question - Indexes on Tables in a view - If I have the following query as a view: [code="sql"] select b.col1, b.col2, b.col3, b.col4, b.col5 from a inner join b on a.id = b.fk_id [/code] Does...

UNIQUEIDENTIFIER vs BIGINT - Hi, I read somewhere that when ever there's a join between two tables, for example orderlines and products being joined by...

Specify schema on table name - Hi, I can across this link on the net [url]http://www.sommarskog.se/dyn-search-2005.html[/url]. Somewhere there's this: [quote] On lines 25-28 there is something very important: FROM dbo.Orders o JOIN...

how to create a database with Windows credential - I was assgined a windows credentials (a user name and password), which only has the permission create a database. But how...

Enforcing data integrity, preventing over-lapping date ranges - Hello All, I typically have join tables where I maintain the current and historical relationships between different entities here represented by...

Protection from SQL Injection with dynamic SQL using SQL 2012 ? - Instead of this: --exec (@Query); I am using this: DECLARE @QueryCondition nVARCHAR(4000) DECLARE @ParmDefinition1 nVARCHAR(4000); SET @ParmDefinition1 = N'@fromtoRec nvarchar'; DECLARE @ParmDefinition2 nVARCHAR(4000); SET...

What SQl Agent Jobs Should I set up? - Hi, What are the most common/best SQL Agent Jobs to set up? The ones I generally set up are as follows:- (Daily...

How to create a procedure which takes multi value parameter -Urgent Help Needed - Hi, I need to create a procedure which takes 2 input parameters and using this stored procedure in crystal reports for...

SQL Serevr Services - Hi All, Can we start and stop the SQL Server services without being the member of Adminstative group on local server...

Why does this take so long! - Hi Everybody This is my first posting in this forum, so please bear with me if I should get some thing...

OS permission override by SQL Server - Hi My windows ID has admin rights on the sql server but I am not the administrator of the server. Now...

How can I find the first column having NULL values in a table - Hi Friends, I have a file reader function that will read the file and load the data into a temp table....

cannot get sql intellisense working after vs2010 update - from scouring the net, this seems to be a big problem, but i've yet to be able to cure it...

SQL Server 2008 : T-SQL (SS2K8)

What does Plan_Handle Specifies? - Hi, I am a newbie.I was going around DMV and found out Plan_Handle. I find out that this column is used...

need help - CREATE TABLE product ( ID INT IDENTITY(1,1) ,VAL VARCHAR(MAX) NOT NULL ); INSERT product ( VAL ) VALUES ( '10/11/2012' ); INSERT product ( VAL ) VALUES ( 'cablegrams' ); INSERT product ( VAL ) VALUES ( 'Set of data' ); INSERT product ( VAL ) VALUES ( '11/15/2012' ); INSERT product ( VAL ) VALUES ( '12/31/0212' ); INSERT produ

Top Operations returns rows in different order when run within a SP - Today we had a problem with a stored procedure in production, here is the code within the SP: SELECT TOP 1 s.SiteID FROM Sites...

t-sql - days to week and days - How do I convert days into weeks and days Ex:350 days = 50 0/7 weeks 351 days = 50 1/7 weeks 352 days...

SQL Server 2000 - Substring or replace for a binary value - How to substring or replace 0x from a binany value 0xF60F9125DBA7E94012F55E2177C95C0E906862B1 in SQL Server 2000 using tsql. The output should be...

trying to change date to use next month - I have the statement below, and I am trying to change the date to use November 1st (11/01/2012). Can't seem...

Running out of Identity values - Ok Long story short, I am running out of Identity values on two tables (different databases) but very big tables, a...

TSQL Case Statement help - Hi All, First off im a TSQL Novice...so help is needed and much appreciated within several tables i have a job type...

Using MERGE for Audting - Hi I'm looking at using Merge which I've never used. I've achieved Auditing before with an After trigger that writes...

Get multiple rows of data based on one condition and grouping - Here is my scenario along with the SQL commands, USE dbo_test --Create a sample table GO CREATE TABLE [dbo].[CustomerPurchase]( [PurchaseDate] [datetime] NOT NULL, [CustomerID] [int] NOT...

Multipe letters into one - All, I have read and asked lot of questionss about "Multiple space into one" here. I just thought the below scenario...

Dynamic Filter and Order By - Hi, I'm doing some testing with dynamic filtering and order by clause. I'm comparing dynamic SQL vs using the CASE statement... [code="sql"] DECLARE @FilterName...

Query to Exclude Duplicate values in different columns. - Can anybody help me with this logic. I want to exclude rows in which value for column nc1 is repeated in...

find increase/decrease amount - i want to find the increase / decrease amount group by sector and month. --input table declare @t table ([id] int,[Amount] int,monthno int,Sector...

Best way to effienctly store hugh amounts of data - Would be interested to hear on some of the best ways to approach this situation. I've recently started at a company...

varchar to time or datetime or ARGGHH Please help me. - I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks...

Multiple Space into one + 65,33,17,9,5,3,2 - All, I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic. He...

Get the overall status of a particular product - Hi, need some help to write the query as i am not sure how to achieve this. I am having a...

How do I use one column for node names and the others for elements in that node? - I'm trying to query some data with FOR XML to output the XML. Let's say I have data like this...

SQL Server 2008 : SQL Server Newbies

Spiltting columns in sql 2008 - hello i have one column that I need split into two, the colum records age value, and the name of...

Date Format in SSRS - I have the date time field. I want extract only Month and the year. How can I do the same. Current...

conditional job step - Hi all, I have a job that has one step (to populate table A). There is an external app that processes...

INVALID OBJECT NAME - Hello, I have wrote one store proc. So, in the stored procedure, it is using one table which is in the...

SQL Server 2008 : Security (SS2K8)

Disabling an account on unexpected SQL Statement - Hi Folks, Wondering if anyone had any ideas on this dilema? I want to be able to trace code through my database,...

Use Windows Logins rather than Windows Group to control access to SQL Server - after doing some researching online...found two different Microsoft sql server 2008 R2 security best practices docs.... one is recommending to use...

SQL Server 2008 : SQL Server 2008 High Availability

Identity Issue - I have a Table where in i have to set Identity in it.... it has 1 Lakh records in it.. when...

sql server agent jobs history - Hi Team, SQL SERVER Agent jobs subplan_1,Plan_2, Plan_3 .. Etc i m not able see jobs history could you please suggest...

Replication - After setting up Replication (Transactional Publication with updatable Subscription) between two instances one instance being both Distributor and Publisher, with...

Mirror and witness becomes disconnected, won't reconnect - Hello all. I have several setups with principal, mirror and witness set to High safety/auto failover. On rare occaisions, the...

Changing Locations of DataFile - Hi All, As i see some of the servers are having databases online... Now this Database have data file & log file on...

SQL Server 2008 : SQL Server 2008 Administration

Rebuild Index failed? - Hi, I am using Mr.ola.hallengren database Maintenance script last 8 months and there is no any issues for backup, rebuild index...

Moving existing indexes to new filegroup,sql server 2008 - Hi, Can anyone of you please guide me how to move indexes (Primary filegroup) to new Index filegroup. I have done for...

urgent help needed--moving data from 2000sql to 2008 sql - [color=#red][/color]I have a database of size 42gb on production which is 2000sql standard edition and want it to move to...

Programming : XML

Converting table data into Table columns + <xmlformat> in last column - I have a Table with 20 columns the first 12 columns needs to be same as it is, but from...

Programming : Service Broker

WMIEventProviderNotificationQueue - Hi all Being new to Service broker I am just trying to understand a couple of points. I am using WMI...

SQLServerCentral.com : Anything that is NOT about SQL!

Windows 2012, whats your thoughts? - Do you love it, hate it?

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Front End Dev - HI all, I'm very happy in my SQL world with all the tools MS give me. Just sometimes somebody wants a...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

SQLServerCentral.com : Editorials

Remote DBAs - There's still hope for all you DBAs out there looking for a telecommuting job that allows you to work in...

Reporting Services : Reporting Services

Lookup Function. Have you tried it? - Hi geniuses! I'm stuck here with a Lookup function in SSRS. Can anybody give me an example of this function? Thanks Best Regards to...

Compare dates & Using Indicators - Hi geniuses! I need to compare dates (proj.last.publication (field) and current date) of various projects and depending of the result, expose...

Lookup - Hi All, Can someone help me with this problem? I have a table with product Id and another table with product...

format date within reporting services - I need to display a date in uk format without the time portion. My report runs from a AS cube...

Data Warehousing : Integration Services

Error on package not sure how to find error - Hi, One of my packages is failing and not sure why? What is the best way to find out what is causing...

Data Warehousing : Data Transformation Services (DTS)

Crystal Report run through DTS - Hi, I just run crystal report daily without any parameter and after run, save the file in c:\temp directory. I have...