Hi all and here we are again with one of the interesting SQL “information_schema”
in this post we will talk about “information_schema.columns” it’s avery interisting view it containes all the data for the tables columns on your DataBase
Here is a list of the column in “information_schema.columns”

Network Related Waits

here is list of wait types.

ASYNC_NETWORK_IO

Occurs on network writes when the task is blocked behind the network

Locking Waits

— LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
— LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock
— LCK_M_S Occurs when a task is waiting to acquire a Shared lock

— *** I/O Related Waits ***
— ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish
— IO_COMPLETION Occurs while waiting for I/O operations to complete.
— This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
— as PAGEIOLATCH_* waits
— PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
— The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
— PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
— The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
— WRITELOG Occurs while waiting for a log flush to complete.
— Common operations that cause log flushes are checkpoints and transaction commits.
— PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
— The latch request is in Exclusive mode.
— BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

— *** CPU Related Waits ***
— SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute.
— During this wait the task is waiting for its quantum to be renewed.

— THREADPOOL Occurs when a task is waiting for a worker to run on.
— This can indicate that the maximum worker setting is too low, or that batch executions are taking
— unusually long, thus reducing the number of workers available to satisfy other batches.
— CX_PACKET Occurs when trying to synchronize the query processor exchange iterator
— You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

— Page Life Expectancy (PLE) value for default instance
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = ‘SQLServer:Buffer Manager’
— Modify this if you have named instances
AND counter_name = ‘Page life expectancy’;

— PLE is a good measurement of memory pressure.
— Higher PLE is better. Below 300 is generally bad.
— Watch the trend, not the absolute value.

Get Buffer cache hit ratio (higher is better)
Buffer cache hit ratio is another measure of memory pressure.
A higher value is better. Below 95% is generally bad.
Watch the trend, not the absolute value.

Find single-use, ad-hoc queries that are bloating the plan cache
Gives you the text and size of single-use ad-hoc queries that waste space in plan cache
Enabling ‘optimize for ad hoc workloads’ for the instance can help (SQL Server 2008 only)
Enabling forced parameterization for the database can help

Missing Indexes for entire SQL instance by Index Advantage
Look at last user seek time, number of user seeks to help determine source and importance
SQL Server is overly eager to add included columns, so beware

Display the first (PgUp) or last (PgDn) command you used in current session

(Enter)

Send the entered lines to PowerShell for execution

(End)

Moves the editing cursor to the end of the command line

(Del)

Deletes the character to the right of the insertion point

(Esc)

Deletes current command line

(F2)

Moves in current command line to the next character corresponding to specified character

(F4)

Deletes all characters to the right of the insertion point up to specified character

(F7)

Displays last entered commands in a dialog box

(F8)

Displays commands from command history beginning with the character that you already entered in the command line

(F9)

Opens a dialog box in which you can enter the number of a command from your command history to return the command. (F7) displays numbers of commands in command history

(Left arrow),(Right arrow)

Move one character to the left or right respectively

(Arrow up),(Arrow down), (F5),(F8)

Repeat the last previously entered command

(Home)

Moves editing cursor to beginning of command line

(Backspace)

Deletes character to the left of the insertion point

(Ctrl)+(C)

Cancels command execution

(Ctrl)+(End)

Deletes all characters from current position to end of command line

(Ctrl)+(Arrow left),(Ctrl)+(Arrow right)

Move insertion point one word to the left or right respectively

(Ctrl)+(Home)

Deletes all characters of current position up to beginning of command line

(Tab)

Automatically completes current entry, if possible

Cmd /c Help

For more information on a specific command, type HELP command-nameASSOC Displays or modifies file extension associations.
AT Schedules commands and programs to run on a computer.
ATTRIB Displays or changes file attributes.
BREAK Sets or clears extended CTRL+C checking.
CACLS Displays or modifies access control lists (ACLs) of files.
CALL Calls one batch program from another.
CD Displays the name of or changes the current directory.
CHCP Displays or sets the active code page number.
CHDIR Displays the name of or changes the current directory.
CHKDSK Checks a disk and displays a status report.
CHKNTFS Displays or modifies the checking of disk at boot time.
CLS Clears the screen.
CMD Starts a new instance of the Windows command interpreter.
COLOR Sets the default console foreground and background colors.
COMP Compares the contents of two files or sets of files.
COMPACT Displays or alters the compression of files on NTFS
partitions.
CONVERT Converts FAT volumes to NTFS. You cannot convert the
current drive.
COPY Copies one or more files to another location.
DATE Displays or sets the date.
DEL Deletes one or more files.
DIR Displays a list of files and subdirectories in a directory.
DISKCOMP Compares the contents of two floppy disks.
DISKCOPY Copies the contents of one floppy disk to another.
DOSKEY Edits command lines, recalls Windows commands, and creates
macros.
ECHO Displays messages, or turns command echoing on or off.
ENDLOCAL Ends localization of environment changes in a batch file.
ERASE Deletes one or more files.
EXIT Quits the CMD.EXE program (command interpreter).
FC Compares two files or sets of files, and displays the
differences between them.
FIND Searches for a text string in a file or files.
FINDSTR Searches for strings in files.
FOR Runs a specified command for each file in a set of files.
FORMAT Formats a disk for use with Windows.
FTYPE Displays or modifies file types used in file extension
associations.
GOTO Directs the Windows command interpreter to a labeled line
in a batch program.
GRAFTABL Enables Windows to display an extended character set in
graphics mode.
HELP Provides Help information for Windows commands.
IF Performs conditional processing in batch programs.
LABEL Creates, changes, or deletes the volume label of a disk.
MD Creates a directory.
MKDIR Creates a directory.
MODE Configures a system device.
MORE Displays output one screen at a time.
MOVE Moves one or more files from one directory to another
directory.
PATH Displays or sets a search path for executable files.
PAUSE Suspends processing of a batch file and displays a message.
POPD Restores the previous value of the current directory saved
by PUSHD.
PRINT Prints a text file.
PROMPT Changes the Windows command prompt.
PUSHD Saves the current directory then changes it.
RD Removes a directory.
RECOVER Recovers readable information from a bad or defective disk.
REM Records comments (remarks) in batch files or CONFIG.SYS.
REN Renames a file or files.
RENAME Renames a file or files.
REPLACE Replaces files.
RMDIR Removes a directory.
SET Displays, sets, or removes Windows environment variables.
SETLOCAL Begins localization of environment changes in a batch file.
SHIFT Shifts the position of replaceable parameters in batch
files.
SORT Sorts input.
START Starts a separate window to run a specified program or
command.
SUBST Associates a path with a drive letter.
TIME Displays or sets the system time.
TITLE Sets the window title for a CMD.EXE session.
TREE Graphically displays the directory structure of a drive or
path.
TYPE Displays the contents of a text file.
VER Displays the Windows version.
VERIFY Tells Windows whether to verify that your files are written
correctly to a disk.
VOL Displays a disk volume label and serial number.
XCOPY Copies files and directory trees.

this is one of the easy posts for me i will talk about how to fixed length digit .
means i have column stored in the DB and i want to show hime with fiexd lenght like “00001 or 00123”

that is pretty much easy using one of the sql functions Called STUFF

this function as discriped in the MSDN as “The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.“

I faced issue on my work we use jquery Dynatreeat one of our projects and for performance issue i decide to use the lazy load feature that comes with Dynatree Plugin ,but i found no example in asp.net to how to achieve this.

So i made an example and will go in details how to make it up and running.

//////[System.Web.Services.WebMethod]//this part is important to make asp convert result to json for us[System.Web.Script.Services.ScriptMethod(ResponseFormat =System.Web.Script.Services.ResponseFormat.Json)]publicstatic List GetNodes(string key){

Add Folder name it Dlls and but the log4net.dll,log4net.xml files and add reference to them on class library project

Run the “Log.sql” Script to your database

2) In the Mvc Project

Add reference to Log4NetWrapper project.

Configure the Log4Net.config file to meet your requirement and here is some example of how to do this.

I configured my own File and here is some keys to understand the file .

I added two Appender “Ways to write logs”

<appendername="DebugAppender"type="log4net.Appender.DebugAppender"><!-- To write on the OutPut Window for Visual Studio To Debug Errors --><appendername="AdoNetAppender_SqlServer"type="log4net.Appender.AdoNetAppender"><!-- To write logs into DataBase -->

I added two Custom Parameters i need to write “[ProjectID],[UserID]”
and to do that i added them to ‘commandText’

5) If the file “Log4Net.config” will be in a custom folder just use this code.

System.IO.FileInfo l4net =newSystem.IO.FileInfo(Server.MapPath("~/Log4Net.config"));//System.IO.FileInfo l4net = new System.IO.FileInfo("Log4Net.config");// if you are using console or desktop application
log4net.Config.XmlConfigurator.ConfigureAndWatch(l4net);

6) To declare the log4net class.

//logger = log4net.LogManager.GetLogger(typeof(Program));//the Program is the name of the current class// or you can use
logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);