T-SQL Tuesday #004: IO — Where Are My TempDB Objects? This blog entry is participating in T-SQL Tuesday #004, hosted this month by Mike Walsh. You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: IO. The question was raised recently in a discussion group about how to tell if your temporary tables and table variables were being maintained in memory or on disk. Here is my attempt to solve
SQLSaturday 26 Session Files: 10/3/2009 in Redmond, WA Thanks to everyone that attended my sessions at SQLSaturday 26 in Redmond, WA on 10/3!! This was my first SQLSaturday event. I was granted the opportunity to be a last minute replacement speaker and gave two presentations. This was my first time speaking in front of a large audience at an event. It was a thoroughly enjoyable experience, and I hope to speak at future events as well. As promised in my
The following was sent to me by my friend and colleague Dave Miller: Dave’s Email: Wanted to pass along something I hadn’t used before and found useful to easily get rid of duplicates in a set of data. The functionality has existed in the SQL language and was supported in SQL Server 2005. This uses Common Table Expressions (CTE) and the ROW_NUMBER() function. The PARTITION BY portion of the statement specifies when to reset the row number, in my example
How Do I Configure SSIS to Work With a Named Instance By default, SSIS (SQL Server Integration Services) uses the msdb database of the default instance on the server for storing packages. SSIS can still manage packages stored as files in the file system without any changes to the current setup. To be able to use the msdb database of a named instance, you have to manually edit the SSIS configuration file. Here are the steps for that: Shut down
SSIS access permissions are managed via Component Services. It is the DCOM object named MSDTSServer. Here are the steps to follow: Run Component Services -> Computers -> My Computer -> DCOM Config -> MSDTSServer Right click on MSDTSServer Click properties Click on the Security tab Select Customize and add the users/groups to the Launch/Activation and Access tabs accordingly Add the user to the local Distributed COM Users group Restart the SSIS service
I’ve got a date with an Error Log — Error Logs Part II In my first post on SQL Server Error Logs, I briefly mentioned using xp_enumerrorlogs to list the archived error logs. Here I want to demonstrate how to use the procedure to find and output all error logs since a specific date. xp_enumerrorlogs This procedure returns 3 columns: Archive #, Date, and Log File Size (Byte). Archive numbering is 0 based with 0 being the currently active log
“Select *” is bad. Everyone knows it, but everyone still uses it. I use it. Most of the time it is fairly innocuous. No harm, no foul, right? But what about those precious milliseconds lost sending data across the network to client applications? That’s where you start to notice the effect of a Select *. This effect is amplified when we deal with tables with large data types such as XML and the new max data types.
How do you know what procedures are cached in SQL Server? Simple, just ask, and SQL Server will tell you. You can query the SQL Server dynamic management views to get a list of procedures in cache. In this example, I query sys.dm_exec_cached_plans and sys.dm_exec_sql_text:
The active SQL Server error log is one of the first places people look when there is a problem. For many people, this is a frustrating exercise. The interfaces for viewing the log in Enterprise Manager and Management Studio are slow and can not be filtered or searched. There is a solution to that problem. You make the error logs fast and searchable by using the undocumented extended stored procedure xp_readerrorlog.