SqlServer Begin's

         This post saved me today. I wanted to change the data and the log folder for Analysis services and mistakenly added an incorrect drive name (D instead of J) and saved from SMSS. I then restarted sql server and obviously sql server couldn’t find the drive location to start the service.                 After identifying the problem ( of the incorrect drive letter )  and finding the workaround I had to change the INI file in the “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER2008R2\OLAP\Config” folder. But surprisingly I couldn’t edit the  “ini” file even after assigning the current user full control of the folder.                 The only way out is to, drag the “ini” file to the desktop, do the required change on the file and drag it back to appropriate folder. Changing the data and log files for Analysis services http://blogs.msdn.com/b/karang/archive/2010/01/19/moving-data-directory-of-analysis-services-sql-2005-2008.aspx editing the “ini” files

Ntext Nvarchar(max) Default Behavior Data is stored as a LOB Stores data in-row Data is stored Out of the data page as   LOB Stored data In-Row ( if less than 4000) Stored as LOB ( when more than 4000) Read-aHead Reads All the rows are read-ahead every time. Does not seems to depend on the cache Didn’t read-ahead during the testing Elapse time to insert 200 records Ex insert into testTable1 select REPLICATE ( 'ABBD455' , 2000 ) go 200 High Low

                                              Very recently I had the opportunity of looking into an environment where the client was using inline code accessibly. Later during the investigation it was reviled they consumed recursive triggers to drive the business logic where views were used as a stepping stone to stage data and procedures/functions were used to implement the logic. (Sound very familiar !!!!!)   As part of my analysis I wanted monitor the sql server cache consumption to identify any potential problems/record the statistics. As the cache consists of two components (Proc cache and Data cache) the analysis had to include both. The two DMV’s used for this are sys.dm_exec_cache_plans and sys.dm_os_buffer_description.  1.)Data cache SELECT   CASE database_id             WHEN 32767 THEN 'RESOURCEDB'             ELSE db_name ( database_id )             END AS 'DatabaseName'             , page_type , Count ( 1 )* 8 / 1024 AS '