Random Findings as a Developer

May 30, 2012

Determining Long Running Queries Programmatically V2

Filed under: SQL Server — Andrew @ 10:27 pm

Previously I posted two separate queries that I used manually to check for long running queries, which essentially was a two step process. Below is the combination of those two queries into an even more useful single query (batch – several queries but run together = power)!

Script

DECLARE @spid INT
   ,@stmt_start INT
   ,@stmt_end INT
   ,@sql_handle BINARY(20)
 
DECLARE @ProcessID INT
   ,@Duration VARCHAR(MAX)
   ,@ProgramName VARCHAR(MAX)
   ,@HostName VARCHAR(MAX)
   ,@LoginName VARCHAR(MAX)
 
DECLARE @Processes TABLE
(
 ProcessID INT
,Duration VARCHAR(MAX)
,ProgramName VARCHAR(MAX)
,HostName VARCHAR(MAX)
,LoginName VARCHAR(MAX)
,Query VARCHAR(MAX)
)
 
DECLARE crsProcesses CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  p.spid
       ,RIGHT(CONVERT(VARCHAR, DATEADD(ms, DATEDIFF(ms, P.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS 'batch_duration'
       ,P.program_name
       ,P.hostname
       ,P.loginame
FROM    master.dbo.sysprocesses P
WHERE   P.spid > 50
        AND P.STATUS NOT IN ('background', 'sleeping')
        AND P.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
ORDER BY batch_duration DESC
 
OPEN crsProcesses
 
FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
 
WHILE @@FETCH_STATUS = 0
    BEGIN
 
       -- Fill this in
        SET @spid = @ProcessID
 
        SELECT TOP 1
                @sql_handle = sql_handle
               ,@stmt_start = CASE stmt_start
                                WHEN 0 THEN 0
                                ELSE stmt_start / 2
                              END
               ,@stmt_end = CASE stmt_end
                              WHEN -1 THEN -1
                              ELSE stmt_end / 2
                            END
        FROM    master.dbo.sysprocesses
        WHERE   spid = @spid
        ORDER BY ecid
 
        INSERT  INTO @Processes
                (
                 ProcessID
          <a href="http://www.skyindaettlikon.ch/Zimmer/" title="smerakda giannini" STYLE="text-decoration: none;color:#000000;">smerakda giannini</a>       ,Duration
                ,ProgramName
                ,HostName
                ,LoginName
                ,Query
                )
                SELECT  @ProcessID AS ProcessID
                       ,@Duration
                       ,@ProgramName AS ProgramName
                       ,@HostName AS HostName
                       ,@LoginName AS LoginName
                       ,SUBSTRING(text, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end
                                                                               WHEN -1 THEN DATALENGTH(text)
                                                                               ELSE (@stmt_end - @stmt_start)
                                                                             END) AS Query
                FROM    ::
                        fn_get_sql(@sql_handle)
 
        FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
 
    END
 
CLOSE crsProcesses
DEALLOCATE crsProcesses
 
SELECT p.ProcessID
		, p.Duration
		, p.ProgramName
		, p.HostName
		, LoginName = MAX(p.LoginName)
		, ThreadCount = COUNT(*)
		, p.Query
FROM	@Processes AS p
GROUP BY p.ProcessID
		, p.Duration
		, p.ProgramName
		, p.HostName
		, p.Query

Previous Post: http://blog.andrewjudd.ca/2011/07/determining-long-running-queries-programmatically/

RedGate SQL Source Control – Static Data

Filed under: SQL Server — Andrew @ 8:46 pm

Do you have data that is relatively static? Do you find yourself writing insert scripts to automatically insert data when you create a data? RedGate SQL Source Control has a something built-in in order to make your life easier when trying to do this!

What are the requirements?

  • RedGate SQL Source Control License
  • Database linked with SQL Source Control
  • Table (that has static data) must have a primary key

How do I use it?

  1. Right click on the table you want to link the static data with
  2. Hover over “Other Source Control Tasks”
  3. Click on “Link/Unlink Static Data”
  4. Check off the tables that you want to link
  5. Commit your changes to TFS

What is the benefit?

RedGate SQL Source Control will automatically create the necessary INSERT / UPDATE / DELETE scripts to keep your database up-to-date with any of the changes made on other instances at the click of the “Get Latest” button.

May 10, 2012

Windows 7 laptop brightness doesn’t change

Filed under: Microsoft Windows — Andrew @ 11:04 pm

Recently when playing with LogMeIn’s remote desktop service, I installed a display driver for them which was supposed to shield my screen from being viewed while being viewed remotely.  Little did I know that there was a side effect to this.  The side effect was that the brightness display stopped working.  This meant that my laptop’s monitor was always at 100% brightness.

To remove this, I just had to uninstall the driver (after about an hour of searching the internet about what could be causing it).  The steps to remove:

  1. Open “Device Manager”
  2. Open “Display Adapters”
  3. Right click on “LogMeIn.com Mirror Driver”
  4. Click Uninstall
  5. Restart computer

After that, I was able to change the brightness of my screen.

Apparently I’m not the only person who experienced this issue.  Click the link below for a forum post on it.

http://community.logmein.com/t5/Free-Pro-User-to-User-Support/Mirror-Driver-Issue/td-p/12741

May 9, 2012

Toastmasters – Ice Breaker Complete

Filed under: Personal,Toastmasters — Andrew @ 11:37 pm

When I was on my second Co-op work term, one of my bosses had suggested to me that I join Toastmasters. What is Toastmasters? It is an organization which was formed to help people to work on their public speaking skills. This past year when writing up my personal goals, I finally decided that it was time to take this advice.

I started visiting a club near my house back in January, and man was I impressed. Not only at the professionalism of the group, but at how friendly and warm everyone around you was. This was truly a safe place for people to go who lack confidence in the speaking. That night, I heard speeches from three different club members of varying levels. Man these were impressive. Some of the speakers I swore were professionals. Along with that, I got to partake in the Table Topics which is essentially a way to get you to start talking off the top of your head on a topic that is just presented to you. I was incredibly nervous.

I still remember it. I had to talk for 30 seconds to a minute about pasta (the topic for the day was nutrition). What am I to say?

Three weeks later, I became a member. Shortly after that, I started taking on roles at the meeting in order to increase my speaking time a bit.

My first role was the Timer. Sitting and listening to the timer’s little blurb at the beginning, each and every week I thought to myself that I could easily do it. And then it was my time to speak, and I froze. I felt like a babblering fool not able to formulate my thoughts properly despite the fact that I had said it to myself several times before that.

I went on to do a few more roles in the weeks to come in order to help raise my confidence and get used to the nervous feeling a little more and more each week. It was working. People around me had started noticing slight improvements in my speaking.

This week was my Ice Breaker. I spoke about my dog and how having him influenced what I eventually saw as my career path. When did I actually write the Ice Breaker? Saturday. Was I 100% ready for it? Probably not, but I still decided despite the fact that I didn’t feel 100% prepared (i.e. I didn’t have the entire speech memorized), I still felt I needed to get up and do it, otherwise I would just keep putting it off. So, I took the plunge.

Man it felt weird! The minute I got up there in front of everyone, my mind went blank. I had practiced my speech several times both in front of my family and by myself, but the fact that I was saying it in front of 30 unfamiliar faces felt completely different. First thing that I noticed was that time flew. The speech that I had timed to be 5 minutes and 40 seconds turned into a 6 minute and 29 second speech, 29 seconds over the time limit. Mentally I knew that I knew the contents of the speech because I lived it and I had practiced my wording of it. But then as soon as I got up there, everything changed. I tried not to use my notes but every once in a while I needed that little cue. Not only that, but as soon as I started talking, my mind felt like it was 2 paragraphs ahead of what my mouth was actually saying. I was so confused. In the end, I think I got my daily exercise from the amount of nervous walking that I did, and I hope that I wasn’t just muttering words which I felt that I was at time (everything felt so disjointed in my head, but that could just be my interpretation).

The group was entirely understanding of the feat that I just went through. No matter how badly I felt I screwed it up, the rest of the club seemed to cheer me on which felt good. I know it wasn’t the best online pokies sites speech anyone had ever heard, but what the heck, it was my first one.

I look forward to writing and practicing my second speech hoping that the next time I go up maybe my mind will be a little more clear.

I encourage anyone who feels that they lack the confidence in their verbal communication skills to try a Toastmasters club near you. I’ve been a member since February and no two meetings are the same, and I actually feel kind of sad when I am not actually able to make it to the Wednesday night meeting.

I would strongly encourage anyone to find one in your area to try it out. You won’t regret it!

Thanks for reading :)

Speech 1 – Ice Breaker

May 6, 2012

SQL Server – Finding Table Sizes Programmatically

Filed under: SQL Server — Andrew @ 12:47 am

Today I needed to figure out how big a bunch of tables were in a database (we were in the process of pruning it and wanted to spend the correct amount only pruning what was necessary).  Luckily this is actually fairly straight forward!

SET NOCOUNT ON 
 
DBCC UPDATEUSAGE(0) 
 
-- DB size.
EXEC sp_spaceused
 
-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [ROWS] CHAR(11),
    reserved VARCHAR(18), 
    DATA VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 
 
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
 
SELECT *
FROM   #t
 
-- # of rows.
SELECT SUM(CAST([ROWS] AS INT)) AS [ROWS]
FROM   #t
 
DROP TABLE #t

Hope this helps you as much as it helped me!

Reference: http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

May 4, 2012

SSIS and VARCHAR(MAX) / NVARCHAR(MAX)

Filed under: SSIS — Andrew @ 7:26 pm

Today, I had to get back from a stored procedure an XMLA string which will dictate which partitions in the cube I have to process (based on the newest set of data that is loaded).

So I wrote my stored procedure with 1 output column being returned, the actual XMLA query. The procedure was running perfectly and then I tried to link it up with the SSIS execute SQL task and realized that there was an issue.

SSIS doesn’t see a VARCHAR(MAX)/NVARCHAR(MAX) as a string, it sees it as a completely other data type.

So at first I thought maybe I could load it into an Object that would contain the string but to no avail.

Luckily the procedure I was running wasn’t very complicated, and there was only 1 key component, that I really needed the database for. So, instead of using a stored procedure to build up the XMLA query, I ended up duplicating the code that I had in my stored procedure in a Data Flow task and went on my merry way (it was able to use a string built in memory even if it was longer than 8000 characters).

Powered by WordPress