Developing Matt

My Technical Journal

Archive for the ‘Uncategorized’ Category

SSIS file name with time

leave a comment »

There is often a need to add a time stamp on your file.  On your file connection (in connection manager section of ssis) pull up the properties for your file (F4 or right click…properties).  Expand expressions and add in a new expression of ‘Connection String.”  From there you should be able to get to your expression builder.  This uses regular expressions. 

From there add your file location, file name and then you can add the parts of your dates with

(DT_WSTR,20) DATEPART("Hh", GETDATE())  +"_" +  (DT_WSTR,20)DATEPART("n", GETDATE()) +"_" +  (DT_WSTR,20)DATEPART("s", GETDATE()) +".txt"

 

DT_WSTR is the casting function and DATEPART pulls out the part of your date.

Result: 8_23_31.txt

Written by matt

August 17, 2012 at 7:24 am

Posted in Uncategorized

The Whacks behave differently, unless you give them some action.

leave a comment »

Had an interesting  situation where utilizing http://websitename/ behaved differently than http://websitename/default.aspx even though the default.aspx page was being used in both instances.  I was using a third party ( infragistics ) grid that was depending on a client script to sort.  When I sorted the page from http://websitename/ it failed, gave me an error stating ‘instance not found’ in the javascript.  When I sorted from http://websitename/default.aspx it worked without a hitch.

At first I thought it was a cacheing issue…that my scripts weren’t being downloaded on even a hard refresh.  But such was not the case.  After some deliberation I turned to senior developer Chris.  He thought for a while and eventually told me to add an action property in the form tag like thus:

<form id="form1" action="default.aspx" runat="server">

This worked.  It enabled the scripts to find default.aspx and utilize the page properties.  I asked for an explanation. Developer Chris said it had something to do with the new IIS 7 and how it has some feature which enables the whacks (/) to behave differently…and to change this behavior would require some add-in on this IIS console. 

If you view the source of a page and look at the action property it will usually be action=”” which isn’t helpful for a script looking for it.  So by specifying the action you can leave the whacks as they are.

 

20100326105835_2010_03_08_kcsunrisekawpark%20(166)

Written by matt

November 21, 2011 at 3:22 pm

Posted in Uncategorized

New photography site

leave a comment »

I like to keep my personal and professional life somewhat separate, but there is this area that falls in the gray between those two lives.  So I set up another blog to exploit it a little. 

I like to take pictures and have started up another site called http://mattclingan.com.  Check it out.  I will be keeping this one technical, but I just thought I’d throw up a post about it.  Thanks for placating.

 

2008_09_15_KellyJDFAM 031 PS

Written by matt

April 21, 2009 at 11:43 am

Posted in Uncategorized

Index Internals and usage by Kimberly Tripp

leave a comment »

If you have heard Kimberly speak, you know she loves to talk about indexes.  So this was a class where she could let loose with her love of indexes and run with it.  And it was great!  Every class i take from her is great.  She is passionate about her topics, and that passion is transferred over to the crowd.  I learned a lot and have much to learn!  There are some major points that she hit below and my notes are here: 01_index-internals-and-usagkimberlytrip.  This class might be difficult to read through.  It’s a good class to sit through and listen to her explan as she goes through the slides, but hopefully it will help.

The point of optimization is not to find a good plan….but to find a good plan fast.  This sometimes means that the best plan is yet to be discovered, so modifying and tweaking and testing your query is a must.

Optimization is about selectivity and what determines selectivity?  Statistics.  In sql 2000 you had to watch the number of statistics but in 2005 the more stats the better. 

Sql server has to know how much data is going to be processed before it goes to the data effeciently. 

Stats on Stats 

  • Stats are stored in sys.stats
  • Stats are not valid between releases (use sp_updatestats)
  • Turn on auto create stats and auto update stats
  • There is a dmv called indexusage
  • Consider adding more statistics using sp_createstats
  • Also recommended: sp_createstats ‘indexonly’,’fullscan’

Indexes…Pages

  • Each page is 8K (8096 if you take away the header(96 bytes)).  There are 400 bytes per row which equates to 20 rows per page
  • How do you see the page count?  SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS TO VIEW INDEX  
  • Sp_helpindex to view indexes
  • A non clustered index is effectively a mini-table, which means they have a lot of interesting uses (which is why an index on last name, first name, middle initial allows you to search for all three or a combination of)
  • Sometimes wider indexes are better than narrower.  If you are seeking highly selective index names then the narrow index is selective enough.  But it’s useless ot have narrow indexes when your queries are returning 1,2,3% of the data. 
  • What performance tools are available?  She mentioned performance dashboard, the dta utility, and umv’s.

Key points

“Always start with a good clustered index key.  If the non-clustered indexes have to look up the data based on the clustereing key that means the clustering key is in every single non-cluster.  So make sure it’s narrow.   if it’s wide it’s going to make all of your non-clusters unnecessarily wide.  And if it’s not static then it has to go to all these places to keep it up to date.  If it’s not unique, sql server ‘uniquifies’ which is highly costly (on insert, updates, times, and space).  Start with a minimal number of non-clusters.  if pk is not the clustered then make the pk the next index. then create your nonclustered unique keys.  Then manually create indexes on your foreign keys.  Then start adding highly selective search arguments.  And then STOP.”

“Narrow indexes have very few uses, so use wider indexes.”

 

Written by matt

June 4, 2008 at 7:05 am

Accidental DBA – Paul Randal, Kimberly Tripp

leave a comment »

I took a class titled ‘Accidental DBA’ by Paul Randal and Kimberly Tripp.  It was my introduction to this couple and I discovered why people love to attend their classes.  It takes a stroke of brilliance to bridge the gap between the inner workings of of a database and translate it into a common language.  Someday I hope to have enough understanding to be able to teach a class of my own.  They are my role models for now.  Soon I will be theirs.  : )   When you view the notes the first is not in any kind of outline.  It is interesting to note that some instructors are more logical in their presentation.  That is, they follow a logical topical path (Paul).   Others present a more referential structure.  They will hit a topic and apply information from a lot of places that apply to that topic (Kimberly).  Neither is correct of corse.  The former provides easier outlining structure, the latter provides a more Jackson Pollock paint splattering structure (try it).  Just as pretty; just different.  I digress.  My notes:    accidentaldbanotes

Highlights and snapshot overview

  • In 2005 manage the temp database. It stores more structures and data than ever before (worktables, sort tables, hashing, userdefined object, table variables, # or ##, triggers)  Also see Brandon Leuthke’s post on top 10 dmvs to use to query tempdb.  For all these reasons isolate the tempdb, preallocate tempdb. 
  • How to move databases: see kb 224071  Whitepaper on working with tempdb
  • dm_db_file_space_usage tells how space is being used in tempdb
  • Autoshrink is evil
  • Autoclose should be turned off
  • Autogrow is ok, but don’t let it get out of control (2005 is better than 2000).  And it can create problems with internal log fragmentation.
  • Optimize log file, only one log file, isolate log file, pre-allocate log, defrag drive where log resides
  • Anatomy of data modification (how it updates)
  • Locks are like breadcrumbs and are OK! (most)
  • See kb 271509 to see about queuing and on how to view who is blocking at the head of the chain (sp_blocer_pss80).  In kb there is a bunch of code that can be used including a dbcc input buffer (shows last command of that blocking user)
  • Nice discussion on transactions and locks and how it works (A transaction is an atomic unit.  When the transaction is complete sql server writes logs to transactions, release locks, notifies user). 
  • Purpose of a checkpoint: reduce roll-forward recovery time…batch i/o to disk….help bring what is closer to current…no guarantee on consistency (only way is with logs)
  • Recovery models discussed
  • Page types (data, index, text, sort, boot, file header, allocation maps, diff map, MI map)
  • How data is stored..the record structure
  • How data is organized (heap, clustered index, non-clustered index, lob data, etc)
  • How data is accessed (singleton lookup, range scan, allocation order scan)
  • The 5 phases of execution
  • Fragmentation and corruption (page splits and wasted space are the cause.)
  • Schemas that use page splits: guids, updates to rows with variable len columns, random insertion points, mismanaged fill factor (whatever the heck that is)
  • Corruption types
  • How to catch corruption: (set alert on error types 823,824,825….turn on pagechecksums…checkdb should be run regularly (at least with physical_only))
  • What to do with corruption

 

     

Written by matt

May 12, 2008 at 1:05 am

What’s new in asp.net 3.5 by Steven Smith

leave a comment »

The class was a pretty good introduction to the new stuff. Attached are my notes. 

There are new controls (listview, datapager to add paging support, linq datasource (bind to any linq enabled data model…(sql right now, but in the future amazone, flickr, text, xml, etc)

The visual studio enhancements are nice (javascript intellisence, multi-targeting support (asp.net 2.0 or 3.5 your choice), control extender support

Other things in the class: asp.net routing engine (similar to url rewriting), asp.net mvc

Regarding Linq: I asked about the overhead of using linq and he didn’t know.  It’s basically an object relational mapper and a little better in performance than a dataset.  All this translates: I won’t use it.  Currently you can’t use edit/update/delete unless you fetc the entire row. (unless you use an anonymous type…you can get one from www.linqpad.net i guess).  One good thing.  It doesn’t run the query until it needs it (which is why paging support is so much better)

 

Written by matt

May 7, 2008 at 9:40 am

Understanding complications, plan reuse and recompilations of stored procedures – Itzik Ben-Gan

leave a comment »

Understanding complications, plan reuse and recompilations of stored procedures – Itzik Ben-Gan

 

Can I just say WOW!  The things I learned in this 1 hour class were phenomenal.  Thanks Itzik for giving me a leg up on optimizing queries.  My notes are here (02_understanding-compliationsstoredproc_itzikbengan)  and his sql scripts are here.

 

PLAN RESUSE

When is a plan reused and what is the possible problem?

The problem is that the cost of reusing an inadequate plan is higher than the cost of recompilation

Any query over 1% of an index is not as efficient as a full table scan

How to query the existing plan: select * from sys.syscacheobjects where sql like ‘%something unique about stored proc. He used newid() to get an ide and added it as a commented line in the stored proc%’

·         Usecounts will show how many times the plan has been used

An example would be to create a stored procedure and then run it for the first time with a highly selective criteria.  The plan that is saved might be ineffective if that’s not a typical criteria. 

Possible solutions:

Create an index that covers the ctriteria(n))

Note: when it doesn’t matter the selectivity it is called a trivial plan and the same plan is always usedc (the optimizer has a flag for this type of plan)

Add recompile to procedure

Specify certain queries not to optimize (add option (recompile) after where clause

The cost of recompile every time is less than the cost of using a wrong plan

 

PARAMETER SNIFFING

The problem is centered around the fact that a declaration inside a stored procedure cannot be ‘sniffed’ (read ahead), therefore it cannot be optimized.  Great discussion on this read notes for solution

 

DYNAMIC FILTERS

This was another great example that showed a common <bad> use of dynamic sql. (example….where name like ‘%@parameter@%’ or  city like ‘%@city%’ or state like ‘%@state%’)  He talked about a way to optimize a query in a clever way.  He also spoke of how to prevent sql injection (use sp_executesql with parameters).  Excellent information I can use today.

Written by matt

May 7, 2008 at 8:53 am