Blog ala Vidar

Chit-chat:

We just had “Pizza & Learning” here in Ølen. My opinion: very successful 🙂 First, Peter startet with showing all available functionality in the grid-control. After half an hour, came FINALLY the pizza-guy. 14(?) HUNGRY computer-geeks to feed!

After some pizza and chit-chat, I started trasking about the new features in SQL 2005, compared to 2000. This includes a couple of new functions and fields (ROW_NUMBER, RANK, VARCHAR(MAX) etc).

But, wait. THERE’S MORE!

Something that can be, in my opinion, VERY useful for developers is the new Dynamic Management Views and Functions (DMV / DMF). This is internal views, which gives you ALOT of (un)usefull information about queries, indexes etc. My example was the following:

SELECT * FROM sys.dm_db_index_physical_stats (5, 665678065, NULL , NULL, 'LIMITED')

5 = DB_ID(‘AppFrameR2’)
665678065 = OBJECT_ID(‘stbl_System_Users’)

This returns everything worth knowing about the indexes on stbl_System_Users. What you should notice here is the “avg_fragmentation_in_percent” column. If this is high, you might want to rebuild your index. This is why I have made a new SP: sstp_System_RebuildIndexes

First time I ran that on AppFrameR2 it took somewhere between 3 and 5 hours (goldfish memory). Now it only takes 30-45 minutes, and I’ve run it each weekend since the first time i ran it. OBS!! I do NOT recommend running this within workhours! This will slow down, and sometimes LOCK several tables! Therefore: Run it at night, when everyone SHOULD be in their bed.

Torgeir’s answer to everything:
One other thing, that’s also available in 2000: PROFILER!

Each time I ask Torgeir (Omegas tech. guru number UNO?) a question related to databases, he ALWAYS (I’m not kidding!) answers: Have you tried the profiler?
… and each time I get that answer, I get red like a tomato and mumble his answer to myself. 90% of the time, this helps, the other times, I ask again, and get a “real” answer 😛

Profiler can help you with alot of things. SQL “debuging” of queries, what happens in stored procedures, checking WHAT’S taking so long and alot of other cool things. If you haven’t tried it: WHY NOT? PS! If anyone’s wondering if I got a “What would Torgeir do” t-shirt: No, actually not. The norwegian mail-service has lost it!

Arild’s cute little query:

SELECT TOP 5 total_worker_time/execution_count 'Avg CPU Time',
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
  ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)statement_text
        FROM sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        ORDER BY total_worker_time/execution_count DESC

You might want to try this cute little query, just for fun. Or you might end up with tuning the result. Who knows 😉
Anyways, open your copy of books online, and in the “Look for:” textbox, write: sys.dm_db
You’ll get a list of dynamic management views and functions. This is excellent reading material on a saturday night, with a glass, or maybe a bottle?, of wine 🙂