Database Reindex

Below is the query to reindex all the tables in a database

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Reindexing: ' + @TableName
        DBCC DBREINDEX(@TableName,' ',90)
        FETCH NEXT FROM TableCursor INTO @TableName
    END
CLOSE TableCursor
DEALLOCATE TableCursor

sp_change_users_login

Encountered a strange issue where i was not able to drop a user and was not able to link this orphan user to an id.


Finally i realized that the SID of the user in the database was not matching with the SID of the user in the windows domain. This was realized when i used the alter user command which gave a more meaningful message as compared to sp_change_users_login

ALTER USER [SDCIT\Ali]     WITH name=[SDCIT\Ali]

the option then was to delete the existing user in the database. However before i could delete the user i had to transfer all its owned objects to dbo.

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('SDCIT\Ali')

-- now use the names you find from the above query below in place of the SchemaName below
ALTER AUTHORIZATION ON SCHEMA::[SDC\Ali] TO dbo

once the user was dropped i was able to create a new user and link it to the windows domain. 

MS SQL - Split a String

This is the first time that i used the apply operator. As the name suggests the apply is an operator hence it works at each row level rather than at a data set level and this comes truly handy to solve the problem on hand.

As defined in TechNet : The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

I wanted to update a worker information and the data provided by the customer has the employee name in a single string using space as separator.

Below is the SQL statement for same.

select TCE.EmpCode   
    , substring( Emp.[Employee Name] , 1, P1.pos ) FirstName
    , case when p2.pos = 0 then '' else substring( Emp.[Employee Name] , P1.pos + 1, P2.pos - P1.pos) end MiddleName
    , case when p2.pos = 0 then substring( Emp.[Employee Name] , P1.pos + 1, len( Emp.[Employee Name] ) - P1.pos)
        else substring( Emp.[Employee Name] , P2.pos + 1, len([Employee Name]) - P2.pos) end LastName  
from TCE
left join EMP
on Emp.[Employee Number] = TCE.EmpCode
cross apply (select (charindex(' ', EMP.[Employee Name] ) ) ) as P1(pos)
cross apply (select (charindex(' ', EMP.[Employee Name] , P1.Pos + 1) ) ) as P2(pos)

AX 2012 Time Profiles

1. Profile :A Time profile is one of the key setups that is required for the time and attendance functionality. A time profile dictates the different types of attendance transactions to expect for a day.

2. Profile Type: A profile type is used to identify how a time interval is classified as either Standard time, overtime, break, and flex time on a given day. A profile type can be configured as one of the 8 profile specification types given below :

1. Clock in : Time when the worker is expected to clock in
2. Clock out: Time when the worker is expected to clock out.
3. Standard time : Time to be considered as work hours during the calculation
4. Overtime : Time to be considered as overtime during the calculation
5. Break : Time to be considered as unpaid break during the calculation
6. Paid Break : Breaks may be paid by the legal entity (Paid break). Otherwise the break time will be deducted from the work time.
7. Flex + : indicates that the flex balance is increased if the worker is at work. Typically, the Flex+ periods are put before clock-in and after clock-out. .
8. Flex -  indicates that hours are deducted from the flex balance if the worker is not at work. Typically, the Flex- periods are put between clock-in and clock-out times.

3. Profile Groups: When there is a need to group a set to time profiles together and set rules for selection of a certain profiles based on date and time constraints then profile groups can be used. 

4. Profile Relation: The profile relation on a profile group defines the profile to be activated for the worker based on their clock in time. In the screen below we can see that from 28 June to 27 July a Ramadan profile is activated which would reduce the work timings during the holy month of Ramadan.

A more visible use of the profile being decided based on the clock in time is the use of the shifts in a company. Consider a scenario when an employee could clock into any of the 3 shift that cover the 24 hours in a day one way to apply the relevant profile would be by using profile relations to decided what time profile to be applied to the worker which in turn would decide his break and overtimes.

4. Profile Calendar: Profile calendar is used to a specific work time profile to one or more workers on a specific date regardless of the clock in time.

In the screen below july 27 has been set as half day for the entire company. This form can also be used to setup holidays.

4.1 Work Planner: In the profile calendar form above there are links available to populate entries into it while compose and copy interval are simple. Work planner is a graphical tool used to create profile calendar entries graphically.

To use the form first set a selection in the list of profiles available (marked 1 in the screen below) then click on the desired cell in the grid (marked 2 in the screen below)

The data changes from this screen would be committed to the profile calendar form however records might not be visible due to the active filters on the form so make sure you open the form from the menu Human Resouce -> Area Page -> Setup -> Time and Attendance-> Time Profiles -> Profile Calendar.

Import a Department Hierarchy

Department hierarchy is one of the key elements of implementing the AX 2012 HR solution.

In this example I have used the DIXF framework for data import. The first thing that i realized is that once a hierarchy is imported it appears as a draft version which has to be published. So don't get worried when you import a hierarchy and you don't see the changes visually on the hierarchy.

The table that stores the import from the DIXF is OMREVISIONEDIT. This can be confirmed looking at the definition of the Organization Hierarchy entity in the DIXF module.

The first issue i encountered is that my legal entity was not getting attached to the department as per the csv file created. As a result i explored the transformation class in DIXF called "DMFOMHierarchyEntityClass" and looked at the function establishing the link called "generateOmParentLink" it didn't take me time to realize that the function was expecting the dataareaid instead of the name in case the organization unit was of type "legal entity"

Once updated success was on its way.

Replace with a part from Find Sting

Editplus probably is the best ascii based text editor that i have experienced so far and the most compelling reason is the find and replace capabilities that this editor allows.

I recently had this task to create an excel sheet full or formulas and just when i started writing these formulas i started wondering if i could manage a shortcut :-)

Here are the details

1. Firstly i wrote the formula on the first row using combination of absolute and relative referencing such that it could be copied to all the rows below while still referring to the right cells.
2. I realized i made a small mistake in the formulas and editing all the formulas again was a daunting task and so the exploration began.
3. I used the Excel feature to display all the formulas in the rows instead of the values using "Ctrl + ~" character combination
4. I copied all the formulas in EditPlus
5. I landed up with a string as below (only a part is pasted the actual string was quite long)

=IF($H4>$AE$1,$V4/(($H4-$G4)/30),0)

=IF($H4>$AF$1,$W4/(($H4-$G4)/30),0)

I wanted to add a AND clause in the formula and a simple find replace would not work as a part of the replace string was variable and had to reproduced from what was being replaced (highlighted in red above). So i used the tagged expression feature of editplus to copy this into the replaced string.

A tagged expression is denoted using round brackets "( )" and can be referred in the replace string using \1. So i used the below expressions

Note: a backslash is used before the $ symbol as it is a special character and the backslash is used to tell editplus to ignore the special meaning $ symbol has to it.

Find: \$H4>(\$....)

Replace : AND($H4>\1,$G4<=\1)

and got the result

=IF(AND($H4>$AE$1,$G4<=$AE$1),$V4/(($H4-$G4)/30),0) =IF(AND($H4>$AF$1,$G4<=$AF$1),$W4/(($H4-$G4)/30),0)

Access Datasheet Change font, or font style, size, and color

An access form is different from a form created in any other environment that i have seen in the sense that the form is does not have a fixed design and the same form design could open in a datasheet (grid) or a Single Form mode while the cosmetic changes on the form are retained in the single form mode during the grid mode the appearance parameters reset to system defaults

The visual parameters like Font, Style, Size and color for a form in a datasheet mode can be changed at the run time by clicking the Home Ribbon and changing the font on the toolbar. 

Access 2010 Data Macros and LocalVars

Data macros is a new and exiciting additon to the MS Access 2010 offerrings. However i had a tough time getting it to work. Belowe are some of the issues identified working with Data Macros

1. The Lookup A Record Data Block only works if a record is found and does not work if a record is not found. So you cannot compose a contruct which triggers if the lookup fails of yields zero records.
2. The way lookup works is that if the record is found, then code inside of the code block runs (code indented inside of the lookup). If record is not found, then all of that indented (nested) code does not run.
3. The work around is using LocalVar. So one would have to declare a local variable above the lookup construct and then in the lookup set the local variable to a value to identify that a record was found.
4. The [localVars]![VariableName] also have a silly problem when trying to use it. There is actuall no need to prefix any variables in code with Localvars infact if you do the code fails so one has to ensure that the prefix is not used while inside a data macro.

 

Maximize Remote Desktop

Shortcut to maximize the remote desktop window

Alt + Ctrl + Pause/ Break

Financial Ratios

 Financial ratios are important pointers to the performance of a company. Looking at a combination of these ration a lot of conclusion can be drawn about the company's performance and operations. The important financial ratios are as follows

Important Terms:

1. Shareholders Equity: At any given point the equity is the total assets - total liabilities.  
2. Operating Income: it is the income or profit generated by the operations and is calculated as total sales - COGS - operating expenses - depreciation. It is also known as EBIT earnings before interest and taxes.

Debit/Equity: This ratio is calculated as
                 Total Liabilities
             __________________
             Shareholders Equity
 a debt to equity ratio of 5 would mean that for every 1 dollar of equity there is a 5 dollar liability. Any company with a high Debt to equity would have low investor interest.



Equity Ratio: This ratio is similar to the above debt to equity ratio, however the focus here is on assets instead of the liabilities. It is used in central europe and is calculated as follows:
                     Total Equity
                  ______________
                      Total Assets
This ratio helps to detemine how much the shareholders would receive in an event of a company wide liquidation. This ratio is expressed as a percentage of the total assets. Thus an equity ratio of 45% for a company with total assets of 500 $ million would mean that in an event of liquidation all the shareholders put togeather would receive $225 million (45% * $500).



Return on Equity : This ratio is expressed as a percentage and is calculated as
                           Net Income
                 __________________
                  Shareholder's Equity
This ratio is a measure of how efficient a company is at generating profits. A company with higher ROE took lesser investment and generated higher profits and hence would be of interest to investors.


Operating Profit Margin: This ratio is expressed as a percentage and is calculated as
                      Operating Income
                    _______________
                         Total Revenue
This ratio is a measure of how much the company makes on each dollar of sales before interest and taxes. This ratio displays how efficient a companys operations are at making profits. An operating profit margin of 12% would mean that it the operations make $0.12 (before interest and taxes) for every dollar of sales.

Costing Methods

Standard Cost

When to use standard costing to value the inventory depends on two issues. 

Firstly, if you in the business of make to stock manufacturing, making the same things in the same way over and over. Make to stock environments make sense for standards since most things are made over and over again, and are supposed to use the same effort, and have the same input costs. If they don't, management should know about it promptly to get things back on track. That's the role of standard cost variance reporting.

Secondly, do you have the resources on staff that can manage standard costs in a timely manner, before any transactions happen? In early days with the missing sofistication of the business systems Standard costing was an easy substitute for the vast amount of data accumulation required to aggregate actual cost information. Also in business scenarios where there is a possibility of negative stock there might be challenges to cost these negative transactions where standard cost works well.

Average

The average costing method is the preferred method of costing for distribution and other industries where the product cost fluctuates rapidly. The fluctation in the reported profits (monthly/ yearly) are reduced when using this method as the increase in the cost would be lesser when compared to the increase in the average cost.


FIFO

It is very common to use the FIFO method if one trades in foodstuffs and other goods that have a limited shelf life, because the oldest goods need to be sold before they pass their sell-by date.


LIFO

In the LIFO method the last inventory cost is consumed first so in most cases this increases the COGS and hence reduces the margins that are reporting in the P&L, because the cost of goods generally goes up over time; which also reduces the income tax liabilities of the company this method however is used in US but due to the above mentioned flaw is disallowed in non-US countries.

JQuery

This week i have started a new initiative to develop a mobile application on top of Dynamics AX however i want to have this application portable across the different client OS available on the mobiles namely Android, Windows, Apple etc.

The platform adopted for the same is HTML5 and JQuery while HTML5 is the latest version of HTML with some very good enhancements JQuery is a javascript framework which makes it easy to work with javascript.

To begin working on javascript there is a library of javascripts functions that we need to refer on our page. We have different versions of this library the popular ones are from google and mircosoft.  JQuery is activated using the dollar symbol followed by round brackets "$()". Below is the first JQuery page i built for printing "Hello World" :

<!DOCTYPE html>

<head>
<title>Hello World</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

<body>

<div id="divTest1"></div>

<script type="text/javascript">
$("#divTest1").text("Hello, world!");
</script>

</body>

</html>

Dynamics AX Query framework

This is a very important diagram which details the hierarchy of the query classes that are available in Dynamics AX to access data.

Eg
public void myQuery2()

{
Query q;
QueryRun qr;
QueryBuildDataSource qbd;
QueryBuildRange qbr;
;

q = new Query();
qbd = q.addDataSource(TableNum(CustTable));
qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
qbr.value('4005');
qbd.addSortField(FieldNum(CustTable, Name));
qr = new QueryRun(q);
qr.prompt();
pause;
}

Debugging Navision Employee Portal

After a long time today i had a chance to work on the Navision portal the need was to debug a problem as we know the navision really is a 2-tier application and the NAS is only the Navision client running in stealth mode as a service i remember having debugged this application server in the past but was lucky enough to recapitulate the same here is how it is done.

1. Stop the NAS server from the management console
2. Create a new codeunit in the navision client
3. In the run method of the new codeunit instatiate and execute the StartNAS method of the 6810 codeunit with a parameter 'NEP-1' or whatever you have used in your instance of NAS; it should display a message stating that the service has started.
4. That's it the debug mode is now enabled within the navision client now just enable the debugger and start debugging.

Read XML Using XMLReader

Reading an XML file using the XMLReader which should be faster compared to DOM classes

    XmlReader   taskDetails;
    str attributeName, attributeValue;
    int i, j;
    ;


    //using the XML Reader class
    taskDetails = XmlTextReader::newXml( tasks.ScriptText );

    while ( taskDetails.read() )
    {
        switch ( taskDetails.nodeType() )
        {
            case XMLNodeType::Element:
                while ( taskDetails.moveToNextAttribute() )
                {
                    attributeName = taskDetails.name();
                    attributeValue = taskDetails.value();
                   
                    info( strfmt("AttributeName =%1, AttributeValue = %2", taskDetails.name(), taskDetails.value() ) );
                }
                break;

            default:
                info( strfmt("Nodetype = %1, NodeName = %2, NodeValue = %3, InnerXML = %4, readAttributeValue = %5"
                            , taskDetails.nodeType()
                            , taskDetails.name()
                            , taskDetails.value()
                            , taskDetails.readInnerXml()
                            , taskDetails.readAttributeValue()
                            ) ) ;

                while ( taskDetails.moveToNextAttribute() )
                {
                    attributeName = taskDetails.name();
                    attributeValue = taskDetails.value();

                    info( strfmt("AttributeName =%1, AttributeValue = %2", taskDetails.name(), taskDetails.value() ) );
                }

                break;

        }
    }

Read XML using DOM

Please find the X++ code snippet below to read a XML stream using DOM


    XmlDocument xmlDoc;
    XmlNode xmlRoot;
    XmlNodeList xmlRecordList;
    XmlElement xmlRecord;

    XmlNamedNodeMap attributeList;
    str attributeName, attributeValue;
    int i, j;
    ;

    xmlDoc = new XmlDocument();
    xmlDoc.loadXml( tasks.ScriptText );
    xmlRoot = xmlDoc.root();
    xmlRecordList = xmlRoot.childNodes();


    for (i=0; i < xmlRecordList.length(); i++)
    {
        xmlRecord = xmlRecordList.item( i );

        attributeList = xmlRecord.attributes();

        for (j=0; j < attributeList.length(); j++)
        {
            attributeName = attributeList.item(j).name();
            attributeValue = xmlRecord.getAttribute( attributeName ) ;
        }
    }

XML Data Model

To parse a XML string it is very important to correctly understand the data model of XML here is my understanding of the same

XMLNode is a basic object in a DOM tree
XMLDocument class extends the node class and support methods for performing operations on the document as a whole

A node can have multiple childs nodes below it however each node would only have one parent. Each node can have multiple name-value pairs which are known as Attributes. If an application does not require the structure and editing capabilities provided by DOM then XMLReader and XMLWrite classes can be used as they are faster and are meant to provide a non-cached, forward only access to an XML stream.

Further to this a node could be of different types. Identifying the node type helps to determine what actions can be performed and what properties can be set or retrieved. To understand the different types of nodes lets use the example below

There is another common question that i have encountered as to what is the difference between a node and an element as we will see in the example below that an element is infact a node type

Example
<?xml version="1.0"?>
<!-- This is a sample XML document -->
<!DOCTYPE Items [<!ENTITY number "123">]>
<Items>
  <Item>Test with an entity: &number;</Item>
  <Item>test with a child element <more/> stuff</Item>
  <Item>test with a CDATA section <![CDATA[<456>]]> def</Item>
  <Item>Test with a char entity: &#65;</Item>
  <!-- Fourteen chars in this element.-->
  <Item>1234567890ABCD</Item>
</Items>

Input	Output	Node Type
<?xml version="1.0"?>	<?xml version='1.0'?>	XmlNodeType.XmlDeclaration
<!-- This is a sample XML document -->	<!--This is a sample XML document -->	XmlNodeType.Comment
<!DOCTYPE Items [<!ENTITY number "123">]>	<!DOCTYPE Items [<!ENTITY number "123">]	XmlNodeType.DocumentType
<Items>	<Items>	XmlNodeType.Element
<Item>	<Item>	XmlNodeType.Element
Test with an entity: &number;</Item>	Test with an entity: 123	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<Item>	<Item>	XmNodeType.Element
test with a child element	test with a child element	XmlNodeType.Text
<more>	<more>	XmlNodeType.Element
stuff	stuff	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<Item>	<Item>	XmlNodeType.Element
test with a CDATA section	test with a CDATA section	XmlTest.Text
<![CDATA[<456>]]>	<![CDATA[<456>]]>	XmlTest.CDATA
def	def	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<Item>	<Item>	XmlNodeType.Element
Test with a char entity: &#65;	Test with a char entity: A	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<!-- Fourteen chars in this element.-->	<--Fourteen chars in this element.-->	XmlNodeType.Comment
<Item>	<Item>	XmlNodeType.Element
1234567890ABCD	1234567890ABCD	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
</Items>	</Items>	XmlNodeType.EndElement

Using gmail as a SMTP Server

Firstly you will not be able to do a direct telnet as gmail using secure connections so there is a free utility that you can use it is called OpenSSL and works pretty much the same as telnet however you will have to enter your username and password in 64 bit MIME (Multipurpose Internet Mail Extensions) based content transfer encoding with base64

Dont worry if didnt quite understand the last bit :-) this encoding is just a standard used to encode non-text 8 bit and binary data. Sometimes used for text data that frequently uses non-US-ASCII characters. There is an internet site which can encode your text data into MIME base64 using this free online utility http://base64-encoder-online.waraxe.us/

1. To begin with download the OpenSSL for windows from http://code.google.com/p/openssl-for-windows/downloads/list
2. Encode your gmail username and password using the encoding utility
3. Go to the DOC command prompt and start the OpenSSL utililty
openssl s_client -ssl3 -connect smtp.gmail.com:587 -starttls smtp -crlf
Loading 'screen' into random state - done
CONNECTED(0000015C)
depth=1 /C=US/O=Google Inc/CN=Google Internet Authority
verify error:num=20:unable to get local issuer certificate
verify return:0
---
Certificate chain
0 s:/C=US/ST=California/L=Mountain View/O=Google Inc/CN=smtp.gmail.com
i:/C=US/O=Google Inc/CN=Google Internet Authority
1 s:/C=US/O=Google Inc/CN=Google Internet Authority
i:/C=US/O=Equifax/OU=Equifax Secure Certificate Authority
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIDWzCCAsSgAwIBAgIKaM9uMQADAAAirTANBgkqhkiG9w0BAQUFADBGMQswCQYD
VQQGEwJVUzETMBEGA1UEChMKR29vZ2xlIEluYzEiMCAGA1UEAxMZR29vZ2xlIElu
dGVybmV0IEF1dGhvcml0eTAeFw0xMTAyMTYwNDM4MDlaFw0xMjAyMTYwNDQ4MDla
MGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpDYWxpZm9ybmlhMRYwFAYDVQQHEw1N
b3VudGFpbiBWaWV3MRMwEQYDVQQKEwpHb29nbGUgSW5jMRcwFQYDVQQDEw5zbXRw
LmdtYWlsLmNvbTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAzv9SacnXKcAx
+0B4yVH2qdpVUXnCk0Nvkn/y2Y0nkzFaWsWMAkOLTMtsa5yP69pnsYb9tUrs4KiL
DkRnf7RdSVlmpIGAknMAPDY/rja23ltutuN7Q4EjadsY8Q+NX5uWjlPsA5BE8GuI
9JobEl4iGFUAfF+2iOGlwFeFbpCaDJECAwEAAaOCASwwggEoMB0GA1UdDgQWBBQK
stTMUTHcQEpEpgaXuVVXJeIHxjAfBgNVHSMEGDAWgBS/wDDr9UMRPme6npH7/Gra
42sSJDBbBgNVHR8EVDBSMFCgTqBMhkpodHRwOi8vd3d3LmdzdGF0aWMuY29tL0dv
b2dsZUludGVybmV0QXV0aG9yaXR5L0dvb2dsZUludGVybmV0QXV0aG9yaXR5LmNy
bDBmBggrBgEFBQcBAQRaMFgwVgYIKwYBBQUHMAKGSmh0dHA6Ly93d3cuZ3N0YXRp
Yy5jb20vR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkvR29vZ2xlSW50ZXJuZXRBdXRo
b3JpdHkuY3J0MCEGCSsGAQQBgjcUAgQUHhIAVwBlAGIAUwBlAHIAdgBlAHIwDQYJ
KoZIhvcNAQEFBQADgYEAxxXNJTE3LS1vmaqNZcFbNeUQtF/9DHpTfGGTtQCAjeMR
uhwSpAmc3/TxeERkT8cBckQxZWlMn2sHa418+DNv0/0QB4SZs0Fus4mXq/Erz91Y
Ouo+mV5BJSkDXH/qbG6wiBdEIypseBEbG+XJMxTSaYVgUjY313rBbAvQ0Uf7ZGQ=
-----END CERTIFICATE-----
subject=/C=US/ST=California/L=Mountain View/O=Google Inc/CN=smtp.gmail.com
issuer=/C=US/O=Google Inc/CN=Google Internet Authority
---
No client certificate CA names sent
---
SSL handshake has read 1932 bytes and written 337 bytes
---
New, TLSv1/SSLv3, Cipher is RC4-SHA
Server public key is 1024 bit
Compression: NONE
Expansion: NONE
SSL-Session:
Protocol : SSLv3
Cipher : RC4-SHA
Session-ID: F08336D4E6FBF5787A197A46D52333B1B2F19B1DA908C213184DD4797B92E60A

Session-ID-ctx:
Master-Key: 18C7490AA3E755D0BCE0EFB80CF99FD0D245A0C6FE58F4D1AD0176E0D826667B
CB8B5ECCE9AA6C67FE680139FF3C771D
Key-Arg : None
Start Time: 1324960518
Timeout : 7200 (sec)
Verify return code: 20 (unable to get local issuer certificate)
---
250 ENHANCEDSTATUSCODES
AUTH LOGIN
334 VXNlcm5hbWU6
c2hhaUBzaGFpLWFydml4ZS5jb20=
334 UGFzc3dvcmQ6
bjw1JWptPm5+OWtjVjdpRi9IIl5HcEhnaHMwN
235 2.7.0 Accepted
mail from:<snk@ae.columbusit.com>
250 2.1.0 OK b20sm85444224ibj.7
rcpt to: <snk@ae.columbusit.com>
250 2.1.5 OK b20sm85444224ibj.7
data
354 Go ahead b20sm85444224ibj.7
subject:Hello
Hello World from gmail SMTP
.
250 2.0.0 OK 1324960636 b20sm85444224ibj.7
quit
221 2.0.0 closing connection b20sm85444224ibj.7
read:errno=0

Find Missing Indexes

Once again the dynamic management views to rescue. Found this from brent ozar's blog havent really used it as i was working on production environment when i came across this but it would be worth a try to carefully deploy these one by one and check for the usage.


SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') +
CASE WHEN mid.inequality_columns IS NULL THEN
''
ELSE
CASE WHEN mid.equality_columns IS NULL THEN
''
ELSE
','
END + mid.inequality_columns
END + ' ) '
+
CASE WHEN mid.included_columns IS NULL THEN
''
ELSE
'INCLUDE (' + mid.included_columns + ')'
END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock)
ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE
(
migs.group_handle IN (
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC
)
)
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable' )=1
ORDER BY 2 DESC , 3 DESC


click here to view brents video

Identifying indexes which can be deleted

Dynamic management views contain a lot of important information which can be used to manage the data more efficiently. The first instinct when asked to performance tune a database is to add new indexes and improve the not so well performing queries. However a more logical view is to beign with removing the unused indexes first before adding any new ones as adding an index also has an overhead.

When working on indentifying the unsed indexes the following query which is basec on the dynamic management view sys.dm_db_index_usage_stats can be used :

SELECT o.name
, indexname=i.name
, i.index_id
, reads = user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE WHEN s.user_updates < 1 THEN
100
ELSE
1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o
on s.object_id = o.object_id
INNER JOIN sys.schemas c
on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads


The column reads_per_write is used to identify the indexes which are the least used. The indexes which are most often written to and the least read from are the ones which should be gotten rid of. The most useful index is the one which is the most used.