Adding critical logging to your procedures

Here is a little tip that will help produce detailed logging and debugging reference points:

Output the name of the stored procedure when it runs.  Simple.

Procedure names, much like anything else, can change over time.  So how do we keep up changes to those messy output strings?  You don't, Simple again.

Include this little jewel with your print output or logging output and see how easy it is to find your way back to the stored proc:
SELECT OBJECT_SCHEMA_NAME(@@PROCID) as [schema], OBJECT_NAME(@@PROCID) as [procedure]
Maybe really show off what a fancy coder you are by including it in your error handling:
BEGIN CATCH
     SELECT
     OBJECT_SCHEMA_NAME(@@PROCID) as [schema],
     OBJECT_NAME(@@PROCID) as [procedure],    
     ERROR_NUMBER() AS ErrorNumber,     
     ERROR_SEVERITY() AS ErrorSeverity,     
     ERROR_STATE() AS ErrorState,     
     ERROR_PROCEDURE() AS ErrorProcedure,     
     ERROR_LINE() AS ErrorLine,     
     ERROR_MESSAGE() AS ErrorMessage;
END CATCH;   

Find the most recently modified table constraints

USE somedatabasename
GO

SELECT DISTINCT CONSTRAINT_NAME

                                 ,modify_date
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT OUTER JOIN sys.all_objects
ON CONSTRAINT_NAME = name
WHERE TABLE_CATALOG = N'somedatabasename'
ORDER BY modify_date DESC

--complements to Igor Malkiman of Qwest Communications for this code

What is Business Intelligence?

Business Intelligence (BI) refers to skills, technologies, applications and practices used to help a business acquire a better understanding of its commercial context. Business intelligence may also refer to the collected information itself.

BI technologies provide historical, current, and predictive views of business operations. Common functions of business intelligence technologies are reporting, OLAP, analytics, data mining, business performance management, benchmarking, text mining, and predictive analytics.

In a 1958 article, IBM researcher Hans Peter Luhn used the term business intelligence. He defined intelligence as:[1] "the ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal."


In 1989 Howard Dresner (later a Gartner Group analyst) proposed BI as an umbrella term to describe  [2]"concepts and methods to improve business decision making by using fact-based support systems." 

It was not until the late 1990s that this usage was widespread.  More recently with the release of SQL 2005, the marketing department at Microsoft has trademarked the term with the introduction  of their "Business Intelligence" toolset.  Thus many believe BI to be something new on the scene.  Before the term "BI" gained popularity, terms such as Predictive Analytics, Decision Support Systems (DSS), and Executive Information Systems (EIS) covered earlier iterations of the same science.

[1] H. P. Luhn (October 1958). "A Business Intelligence System" (PDF). IBM Journal. http://www.research.ibm.com/journal/rd/024/ibmrd0204H.pdf. Retrieved 2008-07-10.


[2] D. J. Power (2007-03-10). "A Brief History of Decision Support Systems, version 4.0". DSSResources.COM. http://dssresources.com/history/dsshistory.html. Retrieved 2008-07-10.

Moving to SQL2008 64-bit

Moving from SQL2005 32-bit to SQL2008 64-bit sounded very exciting at first. Especially when we managed to budget a box with 32gb of memory, 24 processor cores, and 2 raid controllers hitting an array of 16 drives. Our old box was just quad-core, 32-bit, 12gb of ram, and a single controler on a 5-disk array. It did the job, but very slowly.

Imagine my surprise when the database was copied to the new server and the jobs that had run fine on the old box started filling up the 160 gigabytes of TempDB every time they were executed!!!!!!

Long story, but here is the short answer: Rebuild your indexes - because when you move to a 64-bit OS, it has issues reading a fragmented index that was created on a 32-bit system.