tag:blogger.com,1999:blog-44448260826575992312024-02-07T16:18:34.264-08:00My experiments with .NET and SQL ServerZainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-4444826082657599231.post-82884536069635676902011-09-28T17:09:00.000-07:002011-09-28T17:21:21.356-07:00Hide UserName and Password with SQLCMD<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">SQLCMD is a great tool. We have certain scripts that uses sqlcmd .The best way to connect to SQL Server database using sqlcmd would be to use Windows Authentication (i.e.; with –E switch).Unfortunately Windows Authentication won’t work everywhere in enterprise when you have distributed databases running in different domain and under different RDBMS.So we had to use SQL Server Authentication and the username and password were hardcoded with sqlcmd. <o:p></o:p></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">One of the concern security team had was the username and password are hardcoded in the bat file which execute sqlcmd script and that should be avoided. Application normally saves their setting in registry and then gets the information from there. We were also looking for a way on how to do this with SQLCMD.<o:p></o:p></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">One of the article from MSDN which I found very helpful was<o:p></o:p></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><a href="http://msdn.microsoft.com/en-us/library/ms188714.aspx"><span style="color: blue; font-family: Calibri;">http://msdn.microsoft.com/en-us/library/ms188714.aspx</span></a><o:p></o:p></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">This had reference of setting environmental variables and then using it with SQLCMD.Specifcally the note was <o:p></o:p></span></div><br />
<div style="vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">If more than one type of variable has the same name, the variable with the highest precedence is used.<o:p></o:p></span></div><br />
<div style="line-height: 140%; margin-left: 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="mso-list: Ignore;">1.<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">System level environmental variables<o:p></o:p></span></div><br />
<div style="line-height: 140%; margin-left: 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="mso-list: Ignore;">2.<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">User level environmental variables<o:p></o:p></span></div><br />
<div style="line-height: 140%; margin-left: 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="mso-list: Ignore;">3.<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Command shell (<b>SET X=Y</b>) set at command prompt before starting <b>sqlcmd</b><o:p></o:p></span></div><br />
<div style="line-height: 140%; margin-left: 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="mso-list: Ignore;">4.<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><b><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">sqlcmd</span></b><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"> <b>-v</b> X=Y<o:p></o:p></span></div><br />
<div style="line-height: 140%; margin-left: 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="mso-list: Ignore;">5.<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><b><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">:Setvar</span></b><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"> X Y<o:p></o:p></span></div><br />
<div style="line-height: 140%; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">SQLCMD gives variables which can be defined like SQLCMDUSER, SQLCMDPASSWORD, SQLCMDINI etc. The msdn <span style="mso-spacerun: yes;"> </span>article has complete list of variables which can be set in environmental variables.</span></div><div style="line-height: 140%; vertical-align: top;"><br />
</div><div style="line-height: 140%; vertical-align: top;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Lets start with connecting to SQL instance with SQLCMD.The screenshot shows the default connection which is Windows Authentication </span></div><div style="line-height: 140%; vertical-align: top;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqj9s6b8XbneE6p7BYi9I4LB_frZbQiW05X-adE22LL6pe7ADA0Z2ng2XXEKAwphLq8uBRoKMtnvumpd3hoSMSCQpDJQuKUJN7mM2aM3-wERrixEWvcEcaX7_fCGOG_ON2sgYhydEaX-Q/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqj9s6b8XbneE6p7BYi9I4LB_frZbQiW05X-adE22LL6pe7ADA0Z2ng2XXEKAwphLq8uBRoKMtnvumpd3hoSMSCQpDJQuKUJN7mM2aM3-wERrixEWvcEcaX7_fCGOG_ON2sgYhydEaX-Q/s400/1.png" width="400" /></a></div><div style="line-height: 140%; vertical-align: top;"><br />
</div><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Lets verify the username which sqlcmd uses to connect.The function SUSER_NAME() will return current user.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjr2knJwKxqU82ZT_nMZDi1jGWt77jDfV9JaoRHp3RLlECQZmzIepdV_AgsbEU6wCu1ZGsYyJZeDjpc5YE9Q-iXsPuzOorZGFGMFsq9WWxHVN_By02yozxXR1_dosOuRFZT3LUDLF0fTUk/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="98" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjr2knJwKxqU82ZT_nMZDi1jGWt77jDfV9JaoRHp3RLlECQZmzIepdV_AgsbEU6wCu1ZGsYyJZeDjpc5YE9Q-iXsPuzOorZGFGMFsq9WWxHVN_By02yozxXR1_dosOuRFZT3LUDLF0fTUk/s400/2.png" width="400" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">So SQLCMD has used windows authentication and <span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"> an inbuilt user account to connect to SQL Instance.</span></span><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Now lets create a sql authenticated id "testsqlcmduser" in SQL Server instance.</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGckN9sNPjYne4mvFMsKhDCzqnILlAna5VMHId89vdYJnffgCsJwL9261HOzbiFbZIUxET2H8yua7CkmQjMVbkGWFFf_2LeHM-GvgVRGyo0CbW1dxHJs8KEWtrlAk3QXIU47f-ah48arA/s1600/3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="326" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGckN9sNPjYne4mvFMsKhDCzqnILlAna5VMHId89vdYJnffgCsJwL9261HOzbiFbZIUxET2H8yua7CkmQjMVbkGWFFf_2LeHM-GvgVRGyo0CbW1dxHJs8KEWtrlAk3QXIU47f-ah48arA/s400/3.png" width="400" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Please note I have selected to enforce password policy though its not needed but still its a good practice to enforce password policy in sql authenticated id.</span></span><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Now lets add this user and its password in system environmental variable.</span></span><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Go to Control Panel->System->Advanced setting and then click on Environment Variables.You would get a screen like this</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigZv2X6fFIvCLB_q9kFbiunwgdhPow7EZ0veQQu8op8Wy6LKb_0s3Ur9yGLJoR4JnppcEzbNLVQVP23a2RuJeI04Q_dxl-1rXFJnylMkQ8XKTXQF3UqGRQwXbvz-sex3yNUJsjia8QSYw/s1600/4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigZv2X6fFIvCLB_q9kFbiunwgdhPow7EZ0veQQu8op8Wy6LKb_0s3Ur9yGLJoR4JnppcEzbNLVQVP23a2RuJeI04Q_dxl-1rXFJnylMkQ8XKTXQF3UqGRQwXbvz-sex3yNUJsjia8QSYw/s400/4.png" width="400" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">You can add the variable in System Profile or current user profile.Click on new button and add the variable SQLCMDUSER and its value as shown below</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGS7G-rhzQobmCIFSM-tML8SHKCMXT7W4spVl5MsdD5ax3OURRgPF-DZXEBDyhv10a04CbgGKu_ApiCuri9gzTOtAQto7xVTalHQLmgnKErdBQs64gsJQqweTvnyos-5y0NxHdgjlUedI/s1600/5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGS7G-rhzQobmCIFSM-tML8SHKCMXT7W4spVl5MsdD5ax3OURRgPF-DZXEBDyhv10a04CbgGKu_ApiCuri9gzTOtAQto7xVTalHQLmgnKErdBQs64gsJQqweTvnyos-5y0NxHdgjlUedI/s400/5.png" width="328" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Similarly add the SQLCMDPASSWORD variable</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSlvo32P30avhs1KwGXGaVzGQ2VFG0dQpBvsWZcrs5_vE5-jTSjtKKYg5Sp-_a76ByebAnL7i5YMLLP56L5GMsKQgho5lihUMLzJTNYfMD0tztJRzwt0gzy5Mp4cvu8vW3fa3NOCkfSC4/s1600/6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSlvo32P30avhs1KwGXGaVzGQ2VFG0dQpBvsWZcrs5_vE5-jTSjtKKYg5Sp-_a76ByebAnL7i5YMLLP56L5GMsKQgho5lihUMLzJTNYfMD0tztJRzwt0gzy5Mp4cvu8vW3fa3NOCkfSC4/s400/6.png" width="361" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Click on OK and Apply button.The environmental variables ar all set.Now lets use it with SQLCMD.</span></span><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Go to command prompt and type sqlcmd and give the SQL Instance Name.</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtE8mXl1tq7fxPqggMrPWnMNe9Q3gJzS2qXv3mSDV0zNb0xl1Os2oMv-s0rjVeZXNosHbuCE2A2W3jZgryOzK0ka3DvgDkeVNzuvoVAVwc6KSx7xI7RW37kWRaeWLC-Kf7Y_99gW0fYTU/s1600/7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="113" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtE8mXl1tq7fxPqggMrPWnMNe9Q3gJzS2qXv3mSDV0zNb0xl1Os2oMv-s0rjVeZXNosHbuCE2A2W3jZgryOzK0ka3DvgDkeVNzuvoVAVwc6KSx7xI7RW37kWRaeWLC-Kf7Y_99gW0fYTU/s400/7.png" width="400" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">As shown in screenshot above it will get connected .Now lets verify what is the userid which sqlcmd used to connect to SQL Server instance</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRMrtcFs07hErpVwMOcq-vw1X9Bx77x-weO4GZqYMqUrCiMQgTry9TlUP_nfiF2ZU4QlWInaXf6xcV-O9-U255ghyphenhyphenBOUr_ySlSQoGBuT1O8xZfmb8Hdw4PhMijAJta7c8cI-nq-GspHAA/s1600/8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRMrtcFs07hErpVwMOcq-vw1X9Bx77x-weO4GZqYMqUrCiMQgTry9TlUP_nfiF2ZU4QlWInaXf6xcV-O9-U255ghyphenhyphenBOUr_ySlSQoGBuT1O8xZfmb8Hdw4PhMijAJta7c8cI-nq-GspHAA/s400/8.png" width="400" /></a></div><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">It has used testsqlcmduser which is the sql authenticated id.How did it worked.We have not even passed the userid and password.Now when you miss -U and -P option sqlcmd checks the system and user profile variable to see if any of the sqlcmd variables are set.If so it uses that to connect to SQL instance.If there are none then it uses windows authentiction to connect to SQL Server instance.</span></span><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Now lets execute some TSQL command with sqlcmd. I have created a small script file with the following TSQL</span></span><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">use Encryption_Test;<br />
select * from dbo.EncryptedCustomer;<br />
select SUSER_NAME();</span></span><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Basically it connect to a database Encryption_Test in my local sql instance which was created to test encryption in SQL Server 2008.The script then executes a select command on a table in that database which has some of the encrypted data.The last line returns the current connected user.</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmyJC3W8unCEinPMlSjpMEWU1s75S7heq1KjBzH0zuqqq2xpMfdY17ayFH_u1lw746x1RxMDz15yZfji-StX4YXPGSjtNmZqrUSrE-qP8msrj9eHDx_0J1wYkhQkyh_2jgB3HD-GzLNRA/s1600/9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmyJC3W8unCEinPMlSjpMEWU1s75S7heq1KjBzH0zuqqq2xpMfdY17ayFH_u1lw746x1RxMDz15yZfji-StX4YXPGSjtNmZqrUSrE-qP8msrj9eHDx_0J1wYkhQkyh_2jgB3HD-GzLNRA/s400/9.png" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">The result is shown below</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibMlgFc5xXPHrP-o42FAcd0hX1NptYVOQn41WppuuYOmpOuAZ6c8oAWlf9FP_h3QghF0Iq9gyKSAhlEQ-WivGYRkh2YatP6W-viwGEGT588BV6MekJVgoqUJ26nnI6W_DbTOpt0gZ1jm8/s1600/10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="355" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibMlgFc5xXPHrP-o42FAcd0hX1NptYVOQn41WppuuYOmpOuAZ6c8oAWlf9FP_h3QghF0Iq9gyKSAhlEQ-WivGYRkh2YatP6W-viwGEGT588BV6MekJVgoqUJ26nnI6W_DbTOpt0gZ1jm8/s400/10.png" width="400" /></a></div><br />
<br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><span style="font-family: "Calibri","sans-serif"; font-size: 11pt; line-height: 140%; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Please note we have not specified username or password.So we are hiding the details which are needed to connect to SQL Server instance.Even we can hide the SQL instance name by adding the value in variable SQLCMDSERVER.</span></span><br />
</div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com1tag:blogger.com,1999:blog-4444826082657599231.post-6939538875274125402011-08-23T12:25:00.000-07:002011-08-23T12:25:32.657-07:00Query timeout expired error in ASP application<div dir="ltr" style="text-align: left;" trbidi="on"> <br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">Timeouts are common in any enterprise level application. With the huge size of database and concurrent users accessing application I am sure most of you might have encountered timeouts in application. <o:p></o:p></span></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">There can be many reasons for timeouts. The prominent ones are blocking or CPU/memory usage going high. Recently I had to work on application that was getting frequent timeout error.The error is shown here</span></span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKFsJDNhLzj3tGXvrcrRHojtjsXvyQArIYbqv0eR1fRqyYH4TRGjybyxoLOxDRPSX05tlMIKkCHaf-GTG9D6KTdmLp5ZsK_Nc14WGXJt9yoNUpajjqPoBWOvzyTG3CH3azb1N-DT9vz4M/s1600/Main-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="137" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKFsJDNhLzj3tGXvrcrRHojtjsXvyQArIYbqv0eR1fRqyYH4TRGjybyxoLOxDRPSX05tlMIKkCHaf-GTG9D6KTdmLp5ZsK_Nc14WGXJt9yoNUpajjqPoBWOvzyTG3CH3azb1N-DT9vz4M/s400/Main-1.png" width="400" /></a></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;"> </span></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">On enquiring the details of application I was informed that it was an old ASP application and the database was running in SQL Server 2005.There is a particular report which when accessed throws the timeout error. A specific stored procedure was executed for this report and the parameter was date range supplied.</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">Like any DBA who has some knowledge on application programming and administration I thought of recommending them to increase the connection/command timeout value in connection string or IIS.But then a question flashed “Is this the best way to handle this issue?”.<o:p></o:p></span></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">The answer was No. Ideally I should monitor the instance for any blocking. If there are no blockings then check the CPU and RAM utilization. Execute a profiler and capture the statements utilizing high CPU and tune them and check if still you get timeouts or not. If still the issue persists then request network admin to start a netmon trace to check if data-packets across network are getting lost.</span></span><span style="font-size: 12pt; line-height: 115%;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">On checking the instance for blocking (blocked processes) I couldn’t find anything. There was one more application on which I was closely working that also has the same issue of timeout but it <span style="mso-spacerun: yes;"> </span>was predominantly due to processes blocking each other. But in this instance I couldn’t find any process that was blocked. Now the next doubt was CPU or memory usage.<o:p></o:p></span></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">To monitor the statements consuming high CPU I decided to start a trace using profiler. Please make sure that whenever working on application related timeout issue the event Attention in profiler is selected. This event would get fired whenever application gets a timeout. You can then check which TSQL statement was executed just prior to this timeout. The events selected in profiler were:</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">Attention</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"></span><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">User Error Message</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">RPC: Starting</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">RPC: Completed</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">SP:Starting</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">SP:Completed</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">SP:StmtStarting</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">SP:StmtCompleted</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">SQL:BatchStarting </span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;">SQL:BatchCompleted</span></span><span style="font-size: 12pt; line-height: 115%;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;"><span style="font-family: Times New Roman;"> </span></span></span></div><span style="font-family: Calibri;"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-size: 12pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">On running the trace what I found was certainly the CPU time, reads and duration for the particular SP was high and I was able to locate the exact TSQL statement which was throwing timeout.</span></div></span><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-family: Times New Roman;"> </span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCVGC2-YRlnlTkCSodt7ZJXCH2UhtpDdWUd4en7o_IVocj9ArZQmwCgMdByhyI5CrlPqP8pjl7dbc3KL-Okv64gRUrzb3oLOjVn97zvYg2dkCeNaoOv611e94U6vgRvthtFKHnMQKjjvo/s1600/profiler.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCVGC2-YRlnlTkCSodt7ZJXCH2UhtpDdWUd4en7o_IVocj9ArZQmwCgMdByhyI5CrlPqP8pjl7dbc3KL-Okv64gRUrzb3oLOjVn97zvYg2dkCeNaoOv611e94U6vgRvthtFKHnMQKjjvo/s400/profiler.png" width="400" /></a></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: 12pt; line-height: 115%;"><span style="font-family: Calibri;"><o:p><span style="font-family: Times New Roman;"> </span></o:p></span></span></div><span style="font-family: Calibri;"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-size: 12pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><span style="font-family: Times New Roman;"> </span></span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-size: 12pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">As shown in the screenshot the update statement was creating issue. The reads were high and it was consuming high CPU cycles. This update was executed inside a while loop and the same trace file <span style="mso-spacerun: yes;"> </span>had details about the reads and CPU of previously completed update statements.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-family: Times New Roman;"> </span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-size: 12pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Now the next part was to check the execution plan of this update statement and create appropriate indexes. I was able to find a scan on a table that was having 4313995 records. Suggested them to create non clustered indexes on this table so that the table scan was replaced by a seek. Once the indexes were created the timeout errors were resolved. On checking the trace again what I found were the CPU time was drastically reduced.</span></div></span><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV-Btvlo-xyUbSeYjJM2TCVQPEYHQ62QYrSh51-sPPOuOx6wK1hPT0R3TcrvwxQTbaFyaYjmqRJXipGRGAn-mu8TN23YJ73zbwM701kzLhv_Wk6sQ5K-HlKcoxeNstiQ2JROBRTSbRg20/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV-Btvlo-xyUbSeYjJM2TCVQPEYHQ62QYrSh51-sPPOuOx6wK1hPT0R3TcrvwxQTbaFyaYjmqRJXipGRGAn-mu8TN23YJ73zbwM701kzLhv_Wk6sQ5K-HlKcoxeNstiQ2JROBRTSbRg20/s400/2.png" width="400" /></a></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><br />
</div><br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-family: Calibri;"><span style="font-size: 12pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">This is one of the reasons for getting timeout errors. I am sure there are many other factors some of them I have explained before (blockings, network issue etc).Normally application developer thinks that these timeout are issues related to database but what I have seen is they are due to bad TSQL code or database/application design.</span><span style="font-family: "Times New Roman","serif"; font-size: 12pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></span></div><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div></div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com1tag:blogger.com,1999:blog-4444826082657599231.post-34236595180858808582011-06-27T15:43:00.000-07:002011-06-27T17:03:35.849-07:00Nested Loop vs Hash Match<div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Arial, Helvetica, sans-serif;">Last week I was called to help a team facing performance issue in one of the production database instance. This was the statement</span></div><span style="color: black;"><span style="color: blue; font-family: Arial, Helvetica, sans-serif;">Our application performance has good overall past 2 weeks, however today we notice that this one single SP is taking 50 secs to execute when the same took 2 secs yesterday .</span></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">The issue was with single stored procedure. The first thing to check was if there is any blocking in the database instance. No, nothing!!!. I couldn’t find any blocked processes in the instance.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">Since the issue was with single stored procedure I started to perform the steps which every DBA will do.</span><span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">Took the list of tables on which this stored procedure is dependent on and checked their </span><span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">fragmentation details. No the tables were not fragmented.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">OK then maybe there would be issue with statistics of these tables. Probably the statistics of tables were not updated. But when I checked the statistics were fine.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: 'Calibri','sans-serif'; font-size: large; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: small;">At this point I decided that a trace needs to be started to capture the execution plan of the queries. On <span style="mso-spacerun: yes;"> </span>analyzing the execution plan I found that a particular select statement doing join on huge tables was using Nested Loop join. I remember the lines from a book written by Grant Fritchey that nested loop join are appropriate for small tables and for large tables a hash match join would be a better join</span>.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9CxLpZWiPU3DY_yowreLtX_x4JsoY9U-h00kJgw0zYsIudXHg7WuoSeYapr2tiJmxddk7BCgVDskYoZmBKs0j0VpC8nTPjvtOBlewj1OfYpbpT3omJa28oSiID_987IelD5Pl-v10EUI/s1600/1.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="130px" i$="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9CxLpZWiPU3DY_yowreLtX_x4JsoY9U-h00kJgw0zYsIudXHg7WuoSeYapr2tiJmxddk7BCgVDskYoZmBKs0j0VpC8nTPjvtOBlewj1OfYpbpT3omJa28oSiID_987IelD5Pl-v10EUI/s400/1.bmp" width="400px" /></a></div><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">Whoop is this the problem. Is optimizer creating a wrong plan? If so why. Do I need to give join hints here and see if optimizer generates a better plan? I was thinking about these all things then suddenly a though flashed across my mind. Why don’t flush the plan cache and see. I use the command</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;"> </span><span style="color: blue; mso-no-proof: yes;">DBCC</span><b><span style="color: black; mso-no-proof: yes;"> FLUSHPROCINDB </span></b><span style="mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">to flush all the procedure cache for the particular database .</span></span><br />
<span style="font-family: Arial;"></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">Once the plan cache was flushed the stored procedure was behaving as expected. It was responding fast. Now I decided to capture trace with execution plan once again<span style="mso-spacerun: yes;"> </span>to check if the nested loop join operator was changed or not. As expected the query was using hash match join and not nested loop.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-hansi-theme-font: minor-latin;">Probably updating the statistics would have resolved the issue but the tables were huge and updating the statistics would have taken more than 20 minutes. I could have tagged the procedure cache to be recompiled by using sp_recompile stored procedure but at that time flushing the procedure cache looked a better option. The changed execution plan is given below.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPZJu4FoSFQcQxaslXUcRYscSKSQZP-Ct8g0hjU8v9lV6FLMDwPUb5r4_ZXK_aZN24NqAA7bb3uhiLf5rIWRp2KCKccNYDWWM1duuPmbcLh2darQMqa-cu7YmCNdeQfhyphenhyphenewrxpTwiu_Qw/s1600/2.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="146px" i$="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPZJu4FoSFQcQxaslXUcRYscSKSQZP-Ct8g0hjU8v9lV6FLMDwPUb5r4_ZXK_aZN24NqAA7bb3uhiLf5rIWRp2KCKccNYDWWM1duuPmbcLh2darQMqa-cu7YmCNdeQfhyphenhyphenewrxpTwiu_Qw/s400/2.bmp" width="400px" /></a></div><span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial;">Nested loop join works better if the tables joined are small.As long as you have small dataset being joined this is the best join that you would expect to see.</span><br />
<br />
<span style="font-family: Arial;">Hash Match joins are often very effecient on large datasets specially if one table is much smaller than the other.It works well on tables which are not sorted on columns used in join condition.Be aware that sometimes it may also be an indication of a missing where clause or an of an index.</span><br />
<br />
<span style="font-family: Arial;">The KB article has the details on how the three join operators normally work</span><br />
<span style="font-family: Arial;"><a href="http://support.microsoft.com/kb/197297">http://support.microsoft.com/kb/197297</a></span><span style="font-family: Arial, Helvetica, sans-serif;"><br />
</span><br />
<br />
</div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com0tag:blogger.com,1999:blog-4444826082657599231.post-92010006374037418932011-05-05T16:22:00.000-07:002011-05-05T16:22:25.736-07:00Disabled jobs run automatically in SQL Server 2005/2008.<div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">A common myth is that there is a bug in SQL Server 2005/2008 that a disabled job will run automatically in <span style="mso-spacerun: yes;"> </span>SQL Server unless its schedule is disabled. I was also thinking in the same way <span style="mso-spacerun: yes;"> </span>till today this is when I decided to test the same.</span></div><div class="separator" style="clear: both; text-align: center;"></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">The first step was to create a SQL Server Test job that executes sp_who2 in its step. The schedule of the job was run daily every minute. As usual job was running every minute.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">Now decided to update the status of job in sysjobs <span style="mso-spacerun: yes;"> </span>table in msdb.I disabled it using the query </span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">update</span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"> sysjobs <span style="color: blue;">set</span> <span style="color: blue;">enabled</span><span style="color: grey;">=</span>0 <span style="color: blue;">where</span> name<span style="color: grey;">=</span><span style="color: red;">'Test_SQLBugJob'</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">The job was disabled as shown in the screenshot.</span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibYmi_fgM1-KswoqECKkReXw-RQ9Z66gEovtA1sHqEkq3LQELIgepWUEDpNPviNJLYr7r7mgDij0f0PnLiEZeAbxk40ARQOO6cB5MfJbURqFUlntMRTtZqdKb0Ca1BX2vjzSLKBgNmgdc/s1600/1.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="263px" j8="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibYmi_fgM1-KswoqECKkReXw-RQ9Z66gEovtA1sHqEkq3LQELIgepWUEDpNPviNJLYr7r7mgDij0f0PnLiEZeAbxk40ARQOO6cB5MfJbURqFUlntMRTtZqdKb0Ca1BX2vjzSLKBgNmgdc/s400/1.bmp" width="400px" /></a></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">But to when I noted the job history what I found was the job was still executing.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">The next thing to do was to update sysjobactivity table since it has a column next_scheduled_run_date so if I can make it null probably the job won’t execute.The query<span style="mso-spacerun: yes;"> </span>given below was executed.<span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"></span></span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">update</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> sysjobactivity <span style="color: blue;">set</span> next_scheduled_run_date<span style="color: grey;">=null</span> <span style="color: blue;">where</span> job_id<span style="color: grey;">=(</span><span style="color: blue;">select</span> job_id <span style="color: blue;">from</span> sysjobs <span style="color: blue;">where</span> name<span style="color: grey;">=</span><span style="color: red;">'Test_SQLBugJob'</span><span style="color: grey;">)</span> </span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">But still after the job was executing as per schedule. Though the next_scheduled_run_date was null but the job will continue running and<span style="mso-spacerun: yes;"> </span>once its executed it will update the next_scheduled_run_date column with a new value.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">Decided to give a try with SQL Server Management Studio. When disabled through SQL Server management studio things were good and the job never ran. Now what<span style="mso-spacerun: yes;"> </span>commands does SQL Server management studio executes.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">Started a profiler and captured the commands. Found that Management studio executes a command</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: maroon; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">sp_update_job.</span><span style="font-family: Calibri;">This stored proc in turn executes<span style="mso-spacerun: yes;"> </span>another stored procedure </span><span style="color: maroon; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">sp_sqlagent_notify</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">which again executes an extended stored procedure</span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"> </span><span style="color: maroon; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">xp_sqlagent_notify </span><span style="font-family: Calibri;">which is in SQLServer library file</span><span style="color: maroon; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"> </span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">xpstar<span style="color: grey;">.</span>dll. </span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">SQLServer agent caches the data in memory.Unless<span style="mso-spacerun: yes;"> </span>sp_aqlagent_notify is executed the cache is not refreshed.When you<span style="mso-spacerun: yes;"> </span>use SSMS then the command gets executed and SQL Server Agent refreshes its cache will the new setting.</span></div><span style="font-family: Calibri;"><div class="MsoNormal" style="margin: 0in 0in 10pt;">One of my friend <span style="mso-spacerun: yes;"> </span>pointed out that the bug is logged in Microsoft lab </div><span style="color: black; font-family: 'Verdana','sans-serif'; font-size: 10pt;"><a href="https://mail.aexp.com/owa/redir.aspx?C=9f7433633e664dd4ac1903149234a92a&URL=https%3a%2f%2fconnect.microsoft.com%2fSQLServer%2ffeedback%2fdetails%2f552707%2fjob-disabled-and-schedule-enabled%23details" target="_blank"><span style="color: purple;">https://connect.microsoft.com/SQLServer/feedback/details/552707/job-disabled-and-schedule-enabled#details</span></a></span><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;">But if the workaround is to restart the SQL Server agent which means its refreshing the cache.So the point here is if you want to disable a SQL Server agent job never execute plain TSQL update statement.Either you use SSMS or execute the stored proc <span style="color: maroon; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">sp_update_job.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"></div></span><br />
<span style="color: maroon; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"><span style="color: black; font-family: Times New Roman; font-size: small;"></span></span> </div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com1tag:blogger.com,1999:blog-4444826082657599231.post-88519169559878708712011-02-18T22:31:00.000-08:002011-02-18T22:31:32.767-08:00SQL Server could not spawn FRunCM thread<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: inherit;">Today got an annoying error with SQL Server 2008.The error when trying to start SQL Server service was </span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", "sans-serif"; line-height: 115%; mso-bidi-font-family: "Segoe UI";"><span style="color: red; font-family: "Trebuchet MS", sans-serif;">SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.</span></span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikNAIB8JTmu6HwQmdkgij5PIYT8Xdzm_kbp8rosiAgHfG9f22obmxeBNsneYtla-V5Zw0OTl6DsG46zrL3w9Iw7dmEHp-Pquci8DiM4TuFZ4e4ubj-bMtYvMaEZDR8yu7xYiwIf2Zm4gY/s1600/Error_.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="165" j6="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikNAIB8JTmu6HwQmdkgij5PIYT8Xdzm_kbp8rosiAgHfG9f22obmxeBNsneYtla-V5Zw0OTl6DsG46zrL3w9Iw7dmEHp-Pquci8DiM4TuFZ4e4ubj-bMtYvMaEZDR8yu7xYiwIf2Zm4gY/s320/Error_.png" width="320" /></a></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", "sans-serif"; line-height: 115%; mso-bidi-font-family: "Segoe UI";"><span style="font-family: Arial, Helvetica, sans-serif;">This error was logged in application log of Windows Event viewer.On checking the Widows system log the error was</span></span></div><span style="font-family: "Trebuchet MS", "sans-serif"; line-height: 115%; mso-bidi-font-family: "Segoe UI";"><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: red;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;"><span style="font-family: "Trebuchet MS", sans-serif;">The SQL Server (SEREENASQL2008) service terminated with service-specific error %%-2146885628</span>.</span></span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: "Trebuchet MS", sans-serif;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">Tried changing the startup account to LocalSystem account but it didn't help to fix the issue.</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: "Trebuchet MS", sans-serif;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">The error logged in both windows event viewer log also was not quite helpful.</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: "Trebuchet MS", sans-serif;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">At this stage I decided to check SQL Server error log which was in the path C:\Program Files\Microsoft SQL Server\MSSQL10.SEREENASQL2008\MSSQL\Log.</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: "Trebuchet MS", sans-serif;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">The SQL Server error log has an entry like</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: "Courier New", Courier, monospace; font-size: x-small;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">2011-02-18 22:57:43.43 spid15s Clearing tempdb database.<br />
2011-02-18 22:57:43.62 Server Error: 26014, Severity: 16, State: 1.<br />
2011-02-18 22:57:43.62 Server Unable to load user-specified certificate [Cert Hash(sha1) "BCF96EB3346FFC3580D3D833B7C39D1021D88152"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.<br />
2011-02-18 22:57:43.62 Server Error: 17182, Severity: 16, State: 1.<br />
2011-02-18 22:57:43.62 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: black;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">Now this was interesting. I remember some two weeks back created a self signed certificate with IIS in my laptop and then installing it. The same certificate was used to enable SSL over IIS and SQL Server.Please note I have IIS and SQL Server in my laptop.</span></span>I also remember deleting this certificate last week.So concluded that there is some issue with certificate generated by SQL Server.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Courier New", Courier, monospace;">From registry editor went to the path</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Courier New", Courier, monospace;">HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MS$SQLINSTANCE_NAME\MSMSQLSERVER\SUPERSOCKETNETLIB</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV6qKz2eWtCnkTvy1I0tHrU6cfqhlPxlLTUhDJSHS6ZVCItTGSQUuq50qWIJlLcNQDsTX6GYYfBh_Rqq0xcGC0XhI2R4U-OtblIoOP_IlhQwUwd1WwujyAClRftiIUzUZVweAo03-XEWo/s1600/reg.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="177" j6="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV6qKz2eWtCnkTvy1I0tHrU6cfqhlPxlLTUhDJSHS6ZVCItTGSQUuq50qWIJlLcNQDsTX6GYYfBh_Rqq0xcGC0XhI2R4U-OtblIoOP_IlhQwUwd1WwujyAClRftiIUzUZVweAo03-XEWo/s320/reg.png" width="320" /></a></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: "Trebuchet MS", sans-serif;"><span style="font-family: "Segoe UI", "sans-serif"; line-height: 115%;">Deleted the value in Certificate key and then restarted the SQL Server service. Whoop it fixed the issue.</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div></span></div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com2tag:blogger.com,1999:blog-4444826082657599231.post-2083379309671308022011-01-14T20:28:00.000-08:002011-01-14T20:45:23.489-08:00Read SQL Server Trace file using C# in VS 2010<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Arial, Helvetica, sans-serif;">DBAs love using the function <span style="color: #6aa84f;">fn_trace_gettable</span><span style="mso-spacerun: yes;"> </span>to read<span style="mso-spacerun: yes;"> </span>contents from a<span style="mso-spacerun: yes;"> </span>trace file . Just pass the path of the trace file to this function and it will show the content of trace file.But what if developers want to read the same using .NET framework.You<span style="mso-spacerun: yes;"> </span>can’t have the luxury of having SQL Server installed in every machine so that you can execute <span style="color: #38761d;">fn_trace_gettable</span>. This function can’t be executed remotely using a linked server.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div><span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Arial, Helvetica, sans-serif;">First thing what you need to do is open VS 2010 and give reference to SQL Server SMO<span style="mso-spacerun: yes;"> </span>assembly Microsoft.SqlServer.ConnectionInfoExtended.dll and Microsoft.SqlServer.ConnectionInfo.dll</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Arial, Helvetica, sans-serif;">Once you have reference to these dll’s in your project then you can use an object of TraceFile class to read the content of TraceFile.</span></div><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"></span></span><span style="color: green;"><span style="color: green;"><span style="color: green; font-family: Arial, Helvetica, sans-serif;">//Reads the trace file </span></span></span><br />
<br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: small;">TraceFile <span style="color: black;">trcFile = </span><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">new</span></span></span><span style="color: black;"> </span><span style="color: #2b91af;"><span style="color: #2b91af;"><span style="color: #2b91af;">TraceFile</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"><span style="color: black; font-family: Arial, Helvetica, sans-serif; font-size: small;">();</span></span></span></span><br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: small;"><span style="color: black;">trcFile.InitializeAsReader(strFileName);</span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;">while</span></span></span><span style="color: black;"> (trcFile.Read())</span></span></span></span></span></span></span></span></span></span><br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="color: #2b91af; font-family: Consolas; font-size: x-small;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: small;"><span style="color: black;">{</span><span style="mso-spacerun: yes;"><span style="color: black;"> </span> </span><span style="mso-spacerun: yes;"> </span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"> string</span></span></span> <span style="color: black;">strTextData = trcFile.GetString(trcFile.</span><span style="color: black;">GetOrdinal(</span><span style="color: #a31515;"><span style="color: #a31515;"><span style="color: #a31515;">"TextData"</span></span></span><span style="color: black;">)).ToString();</span></span></span></span></span></span></span></span></span></span><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: small;"><span style="font-family: Arial, Helvetica, sans-serif;"><span style="mso-spacerun: yes;"> <span style="color: blue;">string</span> </span><span style="color: black;">strApplication=trcFile.GetString(trcFile.</span><span style="color: black;">GetOrdinal(</span><span style="color: #a31515;"><span style="color: #a31515;"><span style="color: #a31515;">"ApplicationName"</span></span></span><span style="color: black;">)).ToString();</span></span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: inherit;"><span style="font-size: small;"><span style="color: black;"><span style="font-family: Arial, Helvetica, sans-serif;">}</span><span style="mso-spacerun: yes;"> </span></span><span style="mso-spacerun: yes;"> </span></span></span></div><span style="color: black;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: small;">trcFile.Close();</span> </span><br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: small;"><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"></span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: small;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">But before reading these values you may need to check if these columns are null by using the code</span></span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: small;"><span style="color: black;">trcFile.IsDBNull(trcFile.</span><span style="color: black;">GetOrdinal(</span><span style="color: #a31515;">"TextData"</span><span style="color: black;">));</span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-size: small;"><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: small;"></span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="color: black; font-family: Arial, Helvetica, sans-serif; font-size: small;">Once you have these values you can load it into a SQL Server remote database and then create reports based on these values.Please don’t forget to include Microsoft.SqlServer.Management.Trace namespace in your code.</span></div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com0tag:blogger.com,1999:blog-4444826082657599231.post-31551711575125801272010-11-18T22:24:00.000-08:002010-11-18T22:24:02.694-08:00What to do when you have performance issues in SQL Server<span style="font-family: "Trebuchet MS", sans-serif;">Till now as DBA one of the issue which I love the most to work or solve is performance issues in SQL Server. I do enjoy when queries goes mad and misbehave.Based on the production issues which I have seen in last 5 years as DBA I have listed some of the steps that you can follow when there is severe issues with performance of SQL Database server.</span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">1) Check the hardware configuration (CPU, RAM).If the server is running on a low RAM or CPU.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">2) Execute sp_who2<span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>to see if there is blocked process in SQL Server. If there are blocking then please note the spid and execute DBCC INPUTBUFFER (spid) that will give you the SQL Statement that is executed. Kill the blocking process to resolve the issue. Always remember that killing a process can create data integrity issues in application if not handled with care.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">3) Start a profiler to capture queries that are taking max duration to run.Please also note the column CPU and IO on profiler.A high CPU/IO consuming queries need to be looked on.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">4) Once you have the query you need to find out why it’s taking long time. There are couples of reasons for that.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;"><span style="mso-spacerun: yes;"> </span>Bad Execution Plan</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>Statistics out of date</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>Index heavily fragmented</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">5) Check the query plan of those queries for any table scan/clustered index scan etc. If possible create appropriate index to avoid the same.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">6) Check the statistics of tables if they are out of date. If so execute Update Statistics command to update the statistics</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">7) Check the fragmentation of indexes. If tables are heavily fragmented then you may need to rebuild the same. If it’s a heap table then a clustered index might need to be created and then dropped.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">8) Try to capture windows counters and sort if it’s a memory, CPU or disk issue. If it’s a memory issue and if you are running on a 32 bit system check if you can enable AWE to allocate more memory to SQL Server otherwise add more RAM. Remember SQL Server, Exchange server are crazy about RAM. They will try to have as much as you can supply.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">9) Check if any of the scheduled jobs are running on the server.Backup jobs or other maintenance jobs will eat up CPU cycles.</span></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: "Trebuchet MS", sans-serif;">10)If SQL Servers are running<span style="mso-spacerun: yes;"> </span>in a<span style="mso-spacerun: yes;"> </span>clustered environment then check if two or more instances are running on<span style="mso-spacerun: yes;"> </span>the same node.If so please check if you can failover one to the </span><span style="font-family: "Trebuchet MS", sans-serif;">other node.</span></div><span style="font-family: "Trebuchet MS", sans-serif;">These are some of the <span style="font-family: "Calibri", "sans-serif"; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">preliminary </span>steps that you can carry out to find the root cause of issue.Depending on the issue you can involve an expert DBA to sort out the problem.</span>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com0tag:blogger.com,1999:blog-4444826082657599231.post-22656469854280363562010-11-12T16:56:00.000-08:002010-11-12T16:56:42.707-08:00Finding the size of Index in SQL Server 2005/2008<span style="font-family: "Trebuchet MS", sans-serif;">sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.What if you need to capture size of individual index on a table.Thats where the DMF sys.dm_db_index_physical_stats</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">comes handy.</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They are avg_record_size_in_bytes and record_count.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">If these columns are multiplied the resultant is the size of that index.</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">The query given below returns the name of Database,ObjectId,Objectname,IndexId,IndexDescription,Size of Index in MB,Last Updated Statistics Date and Avg Fragmentation.</span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="font-family: "Trebuchet MS", sans-serif; font-size: small;">select</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"> DatabaseName<span style="color: grey;"><span style="color: grey;">,</span></span>ObjectId<span style="color: grey;"><span style="color: grey;">,</span></span>ObjectName<span style="color: grey;"><span style="color: grey;">,</span></span>IndexId<span style="color: grey;"><span style="color: grey;">,</span></span>Index_Description<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: magenta;"><span style="color: magenta;">CONVERT</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: blue;"><span style="color: blue;">DECIMAL</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>16<span style="color: grey;"><span style="color: grey;">,</span></span>1<span style="color: grey;"><span style="color: grey;">),(</span></span><span style="color: magenta;"><span style="color: magenta;">sum</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>avg_record_size_in_bytes <span style="color: grey;"><span style="color: grey;">*</span></span> record_count<span style="color: grey;"><span style="color: grey;">)/</span></span><span style="color: blue;"><span style="color: blue;"> </span></span><span style="color: grey;"><span style="color: grey;">(</span></span>1024.0 <span style="color: grey;"><span style="color: grey;">*</span></span> 1024<span style="color: grey;"><span style="color: grey;">)))</span></span></span><span style="color: blue;"><span style="color: blue; font-family: "Trebuchet MS", sans-serif;">as</span></span><span style="font-family: "Trebuchet MS", sans-serif;"> [Size of Index(MB)]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: grey;"><span style="color: grey;">,</span></span>last_updated <span style="color: blue;"><span style="color: blue;">as</span></span> [Statistic last Updated]<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;">Avg_Fragmentation_In_Percent</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: blue;"><span style="color: blue;">from </span></span><span style="color: grey;"><span style="color: grey;">(</span></span> <span style="color: blue;"><span style="color: blue;">SELECT</span></span> <span style="color: blue;"><span style="color: blue;">Distinct</span></span> <span style="color: magenta;"><span style="color: magenta;">DB_Name</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>Database_id<span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span><span style="color: red;"><span style="color: red;">'DatabaseName'</span></span><span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: magenta;"><span style="color: magenta;">Object_ID</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span> ObjectId<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: magenta;"><span style="color: magenta;">Object_Name</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: magenta;"><span style="color: magenta;">Object_id</span></span><span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span> ObjectName<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;">Index_ID </span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: blue;"><span style="color: blue;">as</span></span> IndexId<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;">Index_Type_Desc </span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: blue;"><span style="color: blue;">as</span></span> Index_Description<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;">avg_record_size_in_bytes </span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: grey;"><span style="color: grey;">,</span></span> record_count<span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: magenta;"><span style="color: magenta;">STATS_DATE</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: magenta;"><span style="color: magenta;">object_id</span></span><span style="color: grey;"><span style="color: grey;">,</span></span>index_id<span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span> <span style="color: red;"><span style="color: red;">'last_updated'</span></span><span style="color: grey;"><span style="color: grey;">,</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: magenta;"><span style="color: magenta;">Convert</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: blue;"><span style="color: blue;">Varchar</span></span><span style="color: grey;"><span style="color: grey;">,</span></span><span style="color: magenta;"><span style="color: magenta;">round</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>Avg_Fragmentation_In_Percent<span style="color: grey;"><span style="color: grey;">,</span></span>3<span style="color: grey;"><span style="color: grey;">))</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span> <span style="color: red;"><span style="color: red;">'Avg_Fragmentation_In_Percent'</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: blue;"><span style="color: blue;">FROM</span></span> <span style="color: green;"><span style="color: green;">sys</span></span><span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: green;"><span style="color: green;">dm_db_index_physical_stats</span></span><span style="color: blue;"><span style="color: blue;"> </span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: magenta;"><span style="color: magenta;">db_id</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: red;"><span style="color: red;">'PM_Db'</span></span><span style="color: grey;"><span style="color: grey;">),</span></span> <span style="color: grey;"><span style="color: grey;">NULL,</span></span> <span style="color: grey;"><span style="color: grey;">NULL,</span></span> <span style="color: grey;"><span style="color: grey;">NULL,</span></span> <span style="color: red;"><span style="color: red;">'detailed'</span></span><span style="color: grey;"><span style="color: grey;">)</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"> </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: blue;"><span style="color: blue;">Where</span></span> <span style="color: magenta;"><span style="color: magenta;">Object_id</span></span> <span style="color: grey;"><span style="color: grey;">is</span></span> <span style="color: grey;"><span style="color: grey;">not</span></span> <span style="color: grey;"><span style="color: grey;">null</span></span> <span style="color: grey;"><span style="color: grey;">and</span></span> Avg_Fragmentation_In_Percent <span style="color: grey;"><span style="color: grey;"><></span></span> 0<span style="color: grey;"><span style="color: grey;">)</span></span></span><span style="font-family: "Trebuchet MS", sans-serif;"> T</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: blue;"><span style="color: blue;">group</span></span> <span style="color: blue;"><span style="color: blue;">by</span></span> DatabaseName<span style="color: grey;"><span style="color: grey;">,</span></span>ObjectId<span style="color: grey;"><span style="color: grey;">,</span></span>ObjectName<span style="color: grey;"><span style="color: grey;">,</span></span>IndexId<span style="color: grey;"><span style="color: grey;">,</span></span>Index_Description<span style="color: grey;"><span style="color: grey;">,</span></span>last_updated<span style="color: grey;"><span style="color: grey;">,</span></span>Avg_Fragmentation_In_Percent</span>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com0tag:blogger.com,1999:blog-4444826082657599231.post-26655829040920035852010-11-06T19:16:00.000-07:002010-11-06T19:21:59.092-07:00Database Projects in VS 2010 - Deploy database with values in master tables<span style="font-family: "Trebuchet MS", sans-serif;">Recently went across a situation where I need to deploy a database in SQL Server 2008 using database projects of VS2010.Things were simple since when you build solution with database projects in VS2010 it creates <span style="font-family: "Trebuchet MS", sans-serif;">db schema,db deploy manifest file, db script file etc.</span></span><span style="font-family: "Trebuchet MS", sans-serif;">When database project is deployed using VS 2010 it uses deployment API available in .NET.The link below describes how you can achieve your own solution</span><br />
<br />
<a href="http://blogs.msdn.com/b/bahill/archive/2010/05/04/leveraging-the-visual-studio-2010-database-deployment-api.aspx">http://blogs.msdn.com/b/bahill/archive/2010/05/04/leveraging-the-visual-studio-2010-database-deployment-api.aspx</a><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">The same functionality can be achieved using sqlcmd.You can execute these sql scripts created using sqlcmd (sqlcmd gets installed when SQL Server 2005/2008 is installed).</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">I guess same results can be achieved with VSDBCMD also.Never tested that :-).</span><br />
<br />
<span style="color: #222222; font-family: "Trebuchet MS", "sans-serif"; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><span style="font-family: "Trebuchet MS", sans-serif;">Now things looked challenging when I had to insert data in some tables while deploying the database projects.In simple words if the database is deployed it should create database,database objects also it need to insert some data in master tables.</span></span><span style="color: #222222; font-family: "Arial", "sans-serif"; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><br />
</span><span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: #222222; font-family: "Trebuchet MS", "sans-serif"; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;">Thereafter I found out you can put all your insert statements in Post Deployment Script File.The database projects in VS 2010 will have a file Script.PostDeployment.sql , just put your insert or delete statements in that file and when you deploy the project it will insert data in master tables.</span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="color: #222222; font-family: "Trebuchet MS", "sans-serif"; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"></span><br />
</span><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiboVdd_grlUEU45T21erTvX2BYjPUvzht-KuQlOsvFnMRk0L9_HSBBzp5b1NA_Q7cMRQI1MHJjpgAP5IXvrM7EWaP29ODTzkafa9rnSEjFCB1aHF-RzISi7ySs9j8vSyztz_VofIPmZcM/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" px="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiboVdd_grlUEU45T21erTvX2BYjPUvzht-KuQlOsvFnMRk0L9_HSBBzp5b1NA_Q7cMRQI1MHJjpgAP5IXvrM7EWaP29ODTzkafa9rnSEjFCB1aHF-RzISi7ySs9j8vSyztz_VofIPmZcM/s1600/1.png" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com1tag:blogger.com,1999:blog-4444826082657599231.post-10809362222641462532010-10-29T16:59:00.000-07:002010-10-29T17:03:38.802-07:00Windows OS versioning and problem with @@version<span style="font-family: "Trebuchet MS", sans-serif;">Today I came across a problem in getting the OS name with @@version variable in SQL Server.The @@version variable returns the build of NT engine(say Windows NT 5.2,6.0).</span><span style="font-family: "Trebuchet MS", sans-serif;">If its 5.2 then the OS would be windows 2003 server, 6.0 indicates Vista.</span><br />
<br />
<span style="font-family: Trebuchet MS;">Now if you are running on Windows7 or Windows2008 R2 then @@version will return Windows NT 6.1.</span><br />
<span style="font-family: Trebuchet MS;">This is confusing since DBA can't make out whether the operating System is Windows 7 or Windows2008 R2.<span style="font-family: Trebuchet MS;">The reason is because Windows 2008 R2 uses the same engine of Windows 7.</span></span><br />
<span style="font-family: Trebuchet MS;">The solution is you use command line utility systeminfo.exe which will display detailed configuration information about a computer and its operating system, including operating system configuration, security information, product ID, and hardware properties, such as RAM, disk space, and network cards.</span><br />
<br />
<span style="font-family: Trebuchet MS;">If you are running on SQL Server 2005 or SQL Server 2008 then this utility can be executed using xp_cmdshell.</span><br />
<span style="font-family: Trebuchet MS;"><span style="color: red;"><span style="color: red;"><span style="color: black;">Please don't forget to enable xp_cmdshell using sp_configure</span></span></span></span><br />
<br />
<span style="font-family: Trebuchet MS;"><span style="color: blue;"><span style="color: blue;"><span style="color: black;">Below attached is the sample script that will get you OS Name.</span><br />
<br />
<span style="color: blue;"><span style="color: blue;">create</span></span> <span style="color: blue;"><span style="color: blue;">table</span></span> #tmpOSName</span></span></span><br />
<span style="font-family: Trebuchet MS;"><span style="color: blue;"><span style="color: blue;"><span style="color: grey;"><span style="color: grey;">(</span></span>Details <span style="color: blue;"><span style="color: blue;">varchar</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>8000<span style="color: grey;"><span style="color: grey;">))</span></span></span></span></span><br />
<br />
<span style="font-family: Trebuchet MS;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">insert</span></span> <span style="color: blue;"><span style="color: blue;">into</span></span> #tmpOSName<span style="color: grey;"><span style="color: grey;">(</span></span>Details<span style="color: grey;"><span style="color: grey;">)</span></span></span></span></span><br />
<br />
<span style="font-family: Trebuchet MS;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">exec</span></span> <span style="color: blue;"><span style="color: blue;">master</span></span><span style="color: grey;"><span style="color: grey;">..</span></span><span style="color: maroon;"><span style="color: maroon;">xp_cmdshell</span></span><span style="color: blue;"><span style="color: blue;"> </span></span><span style="color: red;"><span style="color: red;">'systeminfo'</span></span></span></span></span><br />
<br />
<span style="font-family: Trebuchet MS;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="font-size: small;">select</span></span></span> <span style="color: magenta;"><span style="color: magenta;">rtrim</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: magenta;"><span style="color: magenta;">ltrim</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: magenta;"><span style="color: magenta;">SUBSTRING</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>Details<span style="color: grey;"><span style="color: grey;">,</span></span>9<span style="color: grey;"><span style="color: grey;">,</span></span><span style="color: magenta;"><span style="color: magenta;">LEN</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>Details<span style="color: grey;"><span style="color: grey;">))))</span></span> <span style="color: blue;"><span style="color: blue;">from</span></span> #tmpOSName <span style="color: blue;"><span style="color: blue;">where</span></span> Details <span style="color: grey;"><span style="color: grey;">like</span></span> <span style="color: red;"><span style="color: red;">'OS Name%'</span></span></span><br />
<span style="font-family: Trebuchet MS;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="font-size: small;">drop</span></span></span> <span style="color: blue;"><span style="color: blue;">table</span></span> #tmpOSName</span><br />
<br />
<br />
<br />
<span style="font-family: Trebuchet MS;">Please note this is dependent on xp_cmdshell ; so that needs to be enabled first.</span>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com1tag:blogger.com,1999:blog-4444826082657599231.post-41476920003936260742010-10-27T13:18:00.000-07:002010-10-27T13:23:35.621-07:00SSIS job fails:The affinity mask and affinity I/O mask configuration should not conflict<span style="font-family: "Trebuchet MS", sans-serif;">I was called today to help an issue with maintenance plan in SQL Server 2005.The Integrity Job created with SSIS Package was failing. Neither the output file nor Job history had much detail except the error:</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">The package execution returned DTSER_FAILURE</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">To debug the issue I decided <span style="mso-spacerun: yes;">to capture </span>trace with all events under Errors and Warning selected along with </span><span style="font-family: "Trebuchet MS", sans-serif;">SQL:StmtStarting,SQL:StmtCompleted,SQL:BatchStarting,SQL:BatchCompleted ,RPC:Started</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">and RPC:Completed events selected.</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">After analyzing the trace file the error in which I was interested was Error: 5834, Severity: 16, State: 1 and <span style="mso-spacerun: yes;"> </span>just below that it had entry </span><br />
<br />
<span style="color: red; font-family: "Trebuchet MS", sans-serif;">The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.</span><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBse9Wyjg66uMzFQzgVlNHDIN0Qh3NWs_vGBXEJdiCDjHDcEKYwBe-ah34fw1pcG-BNk3rwx838Dn_ixW74aB57_nizZtodUhZkkaQ-OZz-qkSSRT-pyUg4eKAyp7fkeXI0E4sc_GeHOU/s1600/Trace+File.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" closure_uid_2tsfd3="125" height="67" nx="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBse9Wyjg66uMzFQzgVlNHDIN0Qh3NWs_vGBXEJdiCDjHDcEKYwBe-ah34fw1pcG-BNk3rwx838Dn_ixW74aB57_nizZtodUhZkkaQ-OZz-qkSSRT-pyUg4eKAyp7fkeXI0E4sc_GeHOU/s320/Trace+File.bmp" width="320" /></a></div><br />
<div class="MsoNoSpacing" style="margin: 0in 0in 0pt;"><span style="font-family: "Trebuchet MS", sans-serif;">On checking the configuration of the server it was found that the </span><span style="font-family: "Trebuchet MS", sans-serif;">same processor was affinitize for both SQL Server worker thread scheduling and for I/O processing.</span></div><div class="MsoNoSpacing" style="margin: 0in 0in 0pt;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpa1hEZtJhJqYDgCENnxgtb4Jyk8HXDP5eLGfdqn06n3JKUk4YOYk4HgnDvA2FJ_SQ-5wbB9uTW-3Q5wvNFzl75mPE6NJ7Jq65R8PUDOViGkOKrVdeM4foWO8YxyCeZp7UYq1yxM-kIRY/s1600/Processor_Affinty+Before.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" closure_uid_8lejtt="93" height="188" nx="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpa1hEZtJhJqYDgCENnxgtb4Jyk8HXDP5eLGfdqn06n3JKUk4YOYk4HgnDvA2FJ_SQ-5wbB9uTW-3Q5wvNFzl75mPE6NJ7Jq65R8PUDOViGkOKrVdeM4foWO8YxyCeZp7UYq1yxM-kIRY/s320/Processor_Affinty+Before.bmp" width="320" /></a></div><br />
<div class="MsoNoSpacing" style="margin: 0in 0in 0pt;"><span style="font-family: "Trebuchet MS", sans-serif;">Under this configuration when SQL Server executes <span style="color: black; font-family: 'Verdana','sans-serif';">RECONFIGURE statements it throws an exception.</span></span><br />
</div><div class="MsoNoSpacing" style="margin: 0in 0in 0pt;"><span style="font-family: "Trebuchet MS", sans-serif;">I changed these values , set it separately ,restarted SQL Server (and of course SQL Agent)and tried executing <span style="mso-spacerun: yes;"> </span>job .The integrity job succeeded without any error.These affinity value shouldn’t be shared for same processors or you should set <span style="mso-spacerun: yes;"><u><em> </em></u></span><span style="color: black; font-family: 'Trebuchet MS','sans-serif'; mso-bidi-font-family: 'Trebuchet MS';"><strong><u><em>Automatically set processor affinity mask for all processors</em></u></strong> and <em><u><strong>Automatically set I/O affinity mask for all processors</strong></u></em></span> option.</span></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEis8vbgJZXNPimQuSM80WrPCoJCURzjgNr1T2eGee0c9kJGeEJ9l66DNZG4J4B_9k-ogGo22-3g9WXPeT8zmLZdAAvzAFANblqTNnlWbQyW3FdunhhcQk6lZYXXcOKgbYuNK2yeX88fQUo/s1600/Processor_Affinty+After.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="172" nx="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEis8vbgJZXNPimQuSM80WrPCoJCURzjgNr1T2eGee0c9kJGeEJ9l66DNZG4J4B_9k-ogGo22-3g9WXPeT8zmLZdAAvzAFANblqTNnlWbQyW3FdunhhcQk6lZYXXcOKgbYuNK2yeX88fQUo/s320/Processor_Affinty+After.bmp" width="320" /></a></div><br />
<br />
<div class="MsoNoSpacing" style="margin: 0in 0in 0pt;"><br />
</div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com2tag:blogger.com,1999:blog-4444826082657599231.post-16355139700152348942010-10-26T19:12:00.000-07:002010-10-26T19:12:25.445-07:00Help SQL Server to identify each other across internet<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">Recently I bought a new laptop and had SQL Server 2008 installed on that. The old one carried SQL Server 2005 Express edition.</span><br />
<br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">Thought why not try to identify each of these SQL Server instances when both of them are connected to internet or try registering SQL Server express edition instance in the laptop that carry SQL Server 2008.</span><br />
<br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">The first step that you need to do is make sure that the instance which you want to register runs in a static port. There are two types of ports which can be used by SQL Server (Static and Dynamic).Static ports once set are not changed when SQL Server is restarted. Dynamic ports are changed every time SQL Server restarts.</span><br />
<br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">To set a static port Open SQL Server Configuration Manager ->SQL Server Network Configuration->Select the Protocols for your instance->Select <stockticker w:st="on">TCP</stockticker>/IP on the right side and open its properties.Go to IP Addresses tab, scroll down and check if under IP All category you can find <stockticker w:st="on">TCP</stockticker> Dynamic Ports and <stockticker w:st="on">TCP</stockticker> Port.Make sure that you don't have any value in <stockticker w:st="on">TCP</stockticker> Dynamic Ports and enter a value for <stockticker w:st="on">TCP</stockticker> Port (say 2315). Now restart SQL Server instance. SQL Server instance would use run on this port and it woudn't change regardless the number of time SQL instance gets restarted.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8qF-GrSoS7HURjZEKEtIJVYDOnsEzBitc2G7QX8T799Zz1iPblucZHownR8j4kGV3zrexnvjt9AVUYxUcomiv_P9RhadKh37eoDbkKhxkP1d8m8m38zPUParMIb7tPHatYpDAGV_40ng/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="205" nx="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8qF-GrSoS7HURjZEKEtIJVYDOnsEzBitc2G7QX8T799Zz1iPblucZHownR8j4kGV3zrexnvjt9AVUYxUcomiv_P9RhadKh37eoDbkKhxkP1d8m8m38zPUParMIb7tPHatYpDAGV_40ng/s320/a.png" width="320" /></a></div><br />
<br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">The second step is to make sure that an exception is added in firewall settings for the port underwhich SQL Server runs.In our case it would be 2315.</span><br />
<br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">To add exception you need to go to run and execute firewall.cpl. This will open the firewall window using which you can set exception.Click on allow a program through firewall.Go to the exception tab and click on add port.A new window will pop up. Just enter the Port Number and associate it with a name.Click on OK and then apply.Thats all you are all set to access this SQL Server from other computer in internet.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzPG1TPh1JNgB1gKKoBJ6GMH_UfkW0F9tMEOGwsmSBIMqkrtUiXtwXbWmYn1bK15SlD8U1FQPalW1uQwDbBZET1aNVekuMr8Vn4PYgwndmLe1bkCLeN6VE484GLTAcsuLWXSClw_s1Z5Y/s1600/24.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" nx="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzPG1TPh1JNgB1gKKoBJ6GMH_UfkW0F9tMEOGwsmSBIMqkrtUiXtwXbWmYn1bK15SlD8U1FQPalW1uQwDbBZET1aNVekuMr8Vn4PYgwndmLe1bkCLeN6VE484GLTAcsuLWXSClw_s1Z5Y/s320/24.png" width="316" /></a></div><br />
<br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;"><span style="mso-spacerun: yes;"> </span>To verify go to the other computer and open SQL Server Management studio.Try registering the SQL Server for which you have added exception by <span style="mso-spacerun: yes;"> </span>giving IP address and port number.For example if the IP of SQL Server is 167.56.23.45 and port number was 2315 then try accessing it <span style="mso-spacerun: yes;"> </span>as <span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">167.56.23.45,2315.<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;"><span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">If you want to hide IP and</span></span></span></span><br />
<span style="font-family: "Arial", "sans-serif"; font-size: 10pt;"><span style="font-family: "Arial", "sans-serif"; font-size: 10pt;"><span style="font-family: "Arial", "sans-serif"; font-size: 10pt;"><span style="font-family: "Arial", "sans-serif"; font-size: 10pt;">access SQL Server instance with name please add an alias in Client Configuration Utility. </span></span></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><br />
</div>Zainudeenhttp://www.blogger.com/profile/08534188759378143640noreply@blogger.com0