tag:blogger.com,1999:blog-86190662024-03-05T06:30:34.932-08:00SQL This!The blog...Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.comBlogger53125tag:blogger.com,1999:blog-8619066.post-33620777763919732042018-02-12T08:41:00.000-08:002018-02-12T08:41:04.980-08:00SSAS Tabular<span style="font-family: "verdana" , sans-serif;">After a brief stint, I will begin posting some bootstrap articles around </span><br />
<span style="font-family: "verdana" , sans-serif;">Multi Dimensional databases and Tabular. </span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">So if you are working with SSAS, send me your questions via twitter. And I will post some answers here. </span><a class="twitter-follow-button" data-show-count="false" href="https://twitter.com/seecoolguy?ref_src=twsrc%5Etfw">Follow @seecoolguy</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-26278845357903740442016-04-12T07:52:00.001-07:002016-04-12T07:56:28.664-07:00Service Broker and External Activator for Sql Server...<span style="font-size: x-large;">I</span>ntroduced in Sql Server 2005, Microsoft's Service Broker is a Messaging Queue for Sql Server. You can audit tables, trigger events, and even call web services (Using the External Activator).<br />
<br />
Follow this excellent tutorial by <a href="http://devkimchi.com/" title="Dev Kimchi">Dev Kimchi</a> which helps you step by step and clearly explain how to setup your Service Broker messaging queue.<br />
<br />
To get started with the external activator you will require the version for the version of Sql Server you are running, click on any of the Microsoft download links and follow the install instructions:<br />
<br />
<a href="http://www.microsoft.com/en-us/download/details.aspx?id=29065" rel="nofollow" target="_blank">Microsoft® SQL Server® 2012 Feature Pack</a><br />
<a href="http://www.microsoft.com/en-us/download/confirmation.aspx?id=42295" rel="nofollow" target="_blank">Microsoft® SQL Server® 2014 Feature Pack</a> <br />
<br />
One last thing before you get frustrated installing the External Activator service on a remote server. At least for the 2012 version, it still required .Net 3.5 installed on the destination computer/server. If you are trying to install on a Windows 2012 server follow this helpful guide on how to get .Net 3.5 installed now that the latest packs have been released...<a href="http://serverfault.com/questions/705702/installing-net-3-5-on-windows-2012-r2">http://serverfault.com/questions/705702/installing-net-3-5-on-windows-2012-r2</a><br />
<br />
Follow along and enjoy your journey, <br />
<h1 class="entry-title">
Service Broker External Activator for SQL Server Step by Step</h1>
<blockquote>
<ul>
<li><a href="http://devkimchi.com/831/service-broker-external-activator-for-sql-server-step-by-step-2/"></a> <a href="http://devkimchi.com/811/service-broker-external-activator-for-sql-server-step-by-step-1/">Step 1: Service Broker External Activator Service Setup</a></li>
<li><a href="http://devkimchi.com/831/service-broker-external-activator-for-sql-server-step-by-step-2/">Step 2: SQL Server Setup</a></li>
<li><a href="http://devkimchi.com/891/service-broker-external-activator-for-sql-server-step-by-step-3/">Step 3: External Activator Application Development</a></li>
<li><a href="http://devkimchi.com/951/service-broker-external-activator-for-sql-server-step-by-step-4/">Step 4: External Activator Service Configuration</a></li>
<li><a href="http://devkimchi.com/1051/service-broker-external-activator-for-sql-server-step-by-step-5/">Step 5: Putting Them Altogether</a></li>
</ul>
</blockquote>
<br />
Once you are done with the tutorial and you are happy with the results, you may notice the response queue begins to fill up. What to do?<br />
<br />
http://www.madeiradata.com/service-broker-asynchronous-triggers/ <br />
using the logic for closing the dialogs (SB_AT_CloseDialogs) you can set this procedure to clear out any completed dialogs (messages). only the error messages will remain and allow you to troubleshoot by removing what's been processed.<br />
<br />
Enjoy!<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-50883584278006004822016-03-10T08:24:00.002-08:002016-03-11T07:30:28.559-08:00But can you Tuna Fish? Database Optimizations worth reviewing<div class="separator" style="clear: both; text-align: center;">
<a href="http://images.clipartpanda.com/tuna-clipart-yToergdEc.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://images.clipartpanda.com/tuna-clipart-yToergdEc.png" height="154" width="320" /></a></div>
<span style="font-size: x-large;">T</span>here are always new articles about the web that will offer new ways on how to optimize the performance of your database.<br />
<br />
There are hardware focused optimizations that can and do improve your users data access, and you can fix things at the code level too. Then there is optimizing your database engine to squeeze out every last bit of performance.<br />
<br />
Are you just a bit curious on how to get started?<br />
<br />
<br />
A recent blog article targets many areas in your code that you can mitigate to help deliver the best performance, there are also indexing guides which I don't think I've ever covered in my blog, check it out here:<br />
<br />
<a href="https://www.toptal.com/sql/sql-database-tuning-for-developers">https://www.toptal.com/sql/sql-database-tuning-for-developers</a><br />
<br />
which covers these topics, <br />
<ul>
<li><span style="font-size: small;"><span style="font-weight: normal;">Database Optimization (in the Codebase) Indexes</span></span></li>
<li>Tune SQL query performance by avoiding coding loops</li>
<li>Avoid correlated subqueries</li>
<li>Select sparingly</li>
<li>The use of temp tables SQL Server Performance Tuning: Execution Plans</li>
</ul>
<br />
To optimize your existing indexes you can refer to my previous article <a href="http://sqlthis.blogspot.com/2009/08/boosting-performance.html" rel="nofollow">Boosting Performance</a> and review how to get that database back on track! <br />
<br />
cheers!<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-52078626428255196052013-07-24T15:15:00.002-07:002015-05-26T06:48:04.352-07:00Keeping it Real (short) How to shrink a transaction log file<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.clipartbest.com/cliparts/dc6/oXG/dc6oXGdc9.svg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://www.clipartbest.com/cliparts/dc6/oXG/dc6oXGdc9.svg" height="320" width="320" /></a></div>
<span style="font-family: Verdana,sans-serif;">Space, the final frontier .... </span><br />
<span style="font-family: Verdana,sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">The common wisdom on your Database Files is to NEVER shrink your data files. Space is cheap, </span><span style="font-family: Verdana, sans-serif;">but it's worth knowing how to shrink the size of your data files especially when you are running out...</span><br />
<br />
<a href="http://www.codeproject.com/Tips/625760/How-to-truncate-log-file-in-SQL-Server-2008" rel="nofollow"><span style="font-family: Verdana,sans-serif;">How to Truncate Log files in Sql Server 2008</span></a><br />
<br />
<span style="font-family: Verdana, sans-serif;">If your transaction log files are growing beyond their disk size, it's</span><span style="font-family: Verdana, sans-serif;"> important to consider why your log file keeps growing? </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Databases Log Files will use space to quickly write to disk the many operations it requires and thus some file growth is expected when you reach beyond the initial sizing of your database. When you notice continual growth of your transaction log file, you need to start looking at your backup strategy to help manage it. Did you set your recovery model to the appropriate one? Should you be performing more transaction log backups?</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">for more information on the <a href="http://technet.microsoft.com/en-us/library/ms189493.aspx">shrinkfile </a>operation, consider reviewing the tech doc at Microsoft. You can also read a little more on your database recovery model <a href="http://sqlthis.blogspot.com/2008/06/change-your-recovery-model.html">here</a> to ensure you are using the right one for your set of circumstances!</span><br />
<br />
<br />
<span style="font-family: Verdana, sans-serif;">Happy Shrinking!</span><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-36665608505958957402012-07-19T10:57:00.003-07:002013-07-24T15:16:29.721-07:00What's up?<div style="font-family: Verdana,sans-serif;">
I'm coding that's what's up...</div>
<a href="http://xcodethis.blogspot.com/" style="font-family: Verdana,sans-serif;">http://xcodethis.blogspot.com</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com1tag:blogger.com,1999:blog-8619066.post-85718654472228769542011-01-17T11:48:00.001-08:002011-01-31T10:53:09.671-08:00Lighting Quick Table RowCounts for your Tables<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://t3.gstatic.com/images?q=tbn:ANd9GcR6m_WRlPF6tgxaxJPl7zzVVsPF8my4DCzJpE1S0SyfMnv7tPsz"><img style="float: left; margin: 0pt 10px 10px 0pt; cursor: pointer; width: 200px; height: 150px;" src="http://t3.gstatic.com/images?q=tbn:ANd9GcR6m_WRlPF6tgxaxJPl7zzVVsPF8my4DCzJpE1S0SyfMnv7tPsz" alt="" border="0" /></a><br /><br />If you are familiar with TSQL and want to get a row count from your tables you might be tempted to run a simple<br /><blockquote><br /><span style="font-family: courier new;">Select count(*) from myTable</span><br /></blockquote><br />This however can be a lengthy wait if you have millions and millions of records. Not to mention the potential impact on a production system. Instead consider selecting your rowcounts from your system tables<br /><blockquote><br /><span style="font-family: courier new;">SELECT OBJECT_NAME(id), ROWS, indid<br />FROM sysindexes <br />WHERE indid < 2<br />AND OBJECT_NAME(id) IN ('myTableName')</span><br /></blockquote><br />Looking forward beyond Sql Server 2005 and beyond the following select should be used and will provide you with the needed information as system tables access may be depracated<br /><blockquote><br /><span style="font-family: courier new;">SELECT DISTINCT OBJECT_NAME(P.object_id) AS [Name], ROWS FROM sys.indexes I<br />INNER JOIN sys.partitions P ON P.object_id = I.object_id AND P.index_id = I.index_id<br />WHERE I.index_id < 2<br />AND OBJECT_NAME(P.object_id) IN ('myTableName')</span><br /></blockquote><br /><br />So why am I searching for index smaller than 2? In short from the article below you'll find that pure data pages have indid=0, and clustered index pages and the data pages have indid=1, so searching for this information as < 2 covers us. I also took the liberty of adding a distinct. I've not currently read why sometimes there may be duplicate rows, but the objectid of the table may be different and doing a Distinct helps to narrow the results to data you care about.<br /><br />http://support.microsoft.com/kb/75191<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com1tag:blogger.com,1999:blog-8619066.post-84286626630242455492010-08-23T10:44:00.000-07:002010-08-24T16:00:28.699-07:00New Look... New Stuff<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://t0.gstatic.com/images?q=tbn:ANd9GcRB2t24eoxSJOjbSYsHBXXVZh4M7d3yMtywqgJMcBHGR66japo&t=1&usg=__sl-wgtj7fCZNlVQPI-yR83Szwj8="><img style="float:left; margin:0 10px 10px 0;cursor:pointer; cursor:hand;width: 221px; height: 228px;" src="http://t0.gstatic.com/images?q=tbn:ANd9GcRB2t24eoxSJOjbSYsHBXXVZh4M7d3yMtywqgJMcBHGR66japo&t=1&usg=__sl-wgtj7fCZNlVQPI-yR83Szwj8=" border="0" alt="" /></a><br />Hi everyone... So some new things are going to be happening with my blog... I'm going to pick some helpful hints that help me and my fellow DBA's with what we do on a Daily or weekly basis. These should be helpful for all but you are more than welcomed to share your experiences on what Tips help you the most. One thing that is comming is an article on Wait times... I had the pleasure of trying out Confio's Ignite software. And while it does some really great things, such as pointing out where the bottle necks are for performance on your server. It can be pretty pricey and really does not introduce anything you can't already do.<br /><br />I'm planning on rolling out a short but continuous list of I/O wait times what they are and how to use them to boost performance on your SQL Server within the coming weeks, I will not concentrate on Sql Server 2005/2008 alone, I know that is the trend in the tech community but there are still many organizations that use Sql Server 2000, I mean... why upgrade Legacy systems and pay more money when those systems are perfectly fine, and fine tunned? Sometimes all that is needed is a little tunning :)<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-67232087821320593032010-04-26T08:32:00.001-07:002010-04-27T14:58:34.727-07:00iPad Wifi Networking Issues<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://www.zatznotfunny.com/wordpress/wp-content/uploads/2010/01/ipad-with-keyboard-dock-420x408.jpg"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 100px; height: 100px;" src="http://www.zatznotfunny.com/wordpress/wp-content/uploads/2010/01/ipad-with-keyboard-dock-420x408.jpg" border="0" alt="" /></a><br />I had recently been reading a lot about the the wifi issues surrounding the iPad, and though this post is not SQL related, it kinda is for me. You see I was using a Remote Desktop app on my iPad when the connection started to drop out on me. Something new that I had started to experience. I determined that it had to do with bluetooth being enabled. I tried disabling it and the iPad returned to it's normal bandwidth loving behavior... the thing was that I was using a bluetooth keyboard with it to type up my sql... so I had to find an alternate solution... <br /><br />Solution one... you can actually just turn up the brightness on your iPad and it will keep the wifi from going into sleep mode.. wierd right?<br /><br />or in my case so I didn't need to fumble around I decided to changes some wifi settings of my TrendNet Router, I found the settings at the macrumors forum,<br /><br />http://forums.macrumors.com/showthread.php?t=889348&page=6<br /><br />the settings on my wireless that fixed my problem (apparently permanently) <br /><clip><br />Under the Advanced Wireless Settings...<br />Change the Beacon Interval to 75<br />Fragmentation Threshold to 2304<br />RTS Threshold to 2307<br /></end clip><br /><br />To this I say to the original poster (WesleyB).. great find! Oh sure I could just use the virtual keyboard and not mess with my router, and sure I could just turn up the brightness on my device... but I love just picking up the iPad and quickly logging in to my Sql Server and pick up right where I left off. no fuss, no muss...<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-60614811332307619582010-02-17T07:25:00.000-08:002010-02-17T08:04:10.082-08:00New Ranking tutorial<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://aboutblog.net/Images/Wordpress/increase_ranking.png"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 100px; height: 100px;" src="http://aboutblog.net/Images/Wordpress/increase_ranking.png" border="0" alt="" /></a><br />This is kinda cool. I currently do not have a need for it as my job does not require me to write new tsql code on a daily basis anymore. However, when I used to write ranking code it was always a LONG and TEDIOUS process not really because of the Ranking code, but mostly because of what it takes to get to the ranking code. ARG! My Ranking routine believe it or not consisted of a small segment of code with a CURSOR!<br /><br />This is an snippet of code from what I used to use and is actually still in production today<br /><br /><blockquote>declare tstcur CURSOR FOR SELECT id FROM @temp<br />declare @rank as integer<br />declare @tID as integer<br />OPEN tstcur<br />FETCH NEXT FROM tstcur <br />INTO @tID<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> <br /> select @rank = count(*)+1 FROM @temp WHERE final_score > (select final_score from @temp where id = @tID)<br /> insert into @temp (id, Division, responses, max_points, total_points, final_score, rank)<br /> select id, Division, 0, max_points, total_points, final_score, 0 from @temp where id = @tID<br /> update @temp set rank = @rank where id = @tID<br /><br />FETCH NEXT FROM tstcur INTO @tID<br />END<br />CLOSE tstcur<br />DEALLOCATE tstcur</blockquote><br /><br />as you can see it's not really elegant but it DID get the job done. IF you need this type of code you may certainly reproduce it for your own Sql Server 2000 needs. <br /><br />I do prefer the new method of Ranking using Sql Server 2005 (or 2008). That is the Ranking function built right into the TSQL language so the above code would look like this:<br /><blockquote>SELECT RANK() OVER ( <br /> ORDER BY final_score) AS [Rank],<br />id, Division, responses, max_points, total_points, final_score<br />FROM @temp </blockquote><br /><br />I quite like the non-cursor solution, and if you've read other articles by me you'll know that I'm not at all a fan of cursors (they're just plain evil!) :)<br /><br />For a full tutorial on how to use the new Ranking feature... check out<br /><a href="http://www.mssqltips.com/tip.asp?tip=1944&ctc">Ranking Functions in Sql Server 2005 and 2008</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-41645066326725007732009-12-02T07:22:00.000-08:002010-02-23T08:37:36.418-08:00Rename a Sql Server Database<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijSAF0MBRo-iYqRZaq6uZrcHbzyOboO7RvbIfs5-i-zu_CwDgHJC48JOcSM9ukmfWMj70dD3dz85ojMSfq9UPx-tRCEnNqmNtZjWTHO6Dd8_8KryfAm6tHd3TXRiFlhipw6w7N/s1600-h/hello-my-name-is.jpg"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 120px; height: 70px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijSAF0MBRo-iYqRZaq6uZrcHbzyOboO7RvbIfs5-i-zu_CwDgHJC48JOcSM9ukmfWMj70dD3dz85ojMSfq9UPx-tRCEnNqmNtZjWTHO6Dd8_8KryfAm6tHd3TXRiFlhipw6w7N/s320/hello-my-name-is.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5441478740202725074" /></a><br />Often when a database needs to be renamed a common tool that I've used in the past was always to use the sp_renamedb procedure. And that's one of the reasons I love the internet, there is always a better way to do something. Take the tips on how to rename your database without the use of this procedure. What is neat about the following article is that it also changes the logical and physical names of the database. <br />see more at this link...<br />http://www.mssqltips.com/tip.asp?tip=1891<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-46438798345370145032009-08-11T22:13:00.000-07:002009-08-12T15:03:41.709-07:00Boosting Performance<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW2PlOGOXmMbvhyAOM3TDKW7tdRLVdf2U7HfU0Qx6VKIeJUCxD-AjQ7e-C6rKjSIG4Ey4lDklXyhGHkzQt1ezF3IQdfqBLLL6b-HeGjQPdcRp_WBRq_LTKPojapn0XcuvIp2MS/s1600-h/performance.jpg"><img style="float:left; margin:0 10px 10px 0;cursor:pointer; cursor:hand;width: 120px; height: 90px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW2PlOGOXmMbvhyAOM3TDKW7tdRLVdf2U7HfU0Qx6VKIeJUCxD-AjQ7e-C6rKjSIG4Ey4lDklXyhGHkzQt1ezF3IQdfqBLLL6b-HeGjQPdcRp_WBRq_LTKPojapn0XcuvIp2MS/s320/performance.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5369201391583358610" /></a><br /><span style="font-weight: bold;">F</span>ixing smallish databases which are less than 1-2 gb may be just annoying when you are dealing with multiple indexes, but try managing some of those larger ERP databases with literally thousands of tables! Talk about a database from hell, having to sift through 10 of thousands of indexes can be a real chore if you're searching for performance bottle necks. There are some great solutions out there that all cost money per instance or per site license and can get quite pricey. But just about all of those products are charging you for something you can do on your own.<br /><br /><span style="font-weight: bold;">T</span>he article below is an extension of my previous blog on <a href="http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html">maintaining those indexes</a> It's the script that has evolved from some very basic loops and DMV ( dm_db_index_physical_stats ). In my script (follow the link below) You'll find that I chose to stick to a SAMPLED stats, which essentially looks at the number of compressed pages. If you're talking millions of row of data and a very short maintenance window, you're going to want to stick to SAMPLED, otherwise, you'll opt for the full DETAILED search if<br /><br />A) your maintenance window is greater than the amount of time it takes for a DETAIL report to be returned<br />B) your database is small enough<br /><br />One of the problems that I've ran into with my production databases that use a FULL recovery model is the maintaining the size of the transaction logs. The problem is that I COULD switch them over to be SIMPLE recovery models, and then the transaction log would not grow out of control during the rebuild / reorganization (Defrag) of the indexes, but I've found this not to be an ideal situation, since in my organization even though I have a maintenance window on the weekends, the business comes first, so if they require access to their data, I must make it available. Having good backups that allow the company to return up to the minute before failure using the FULL recovery model is important to us. So in the script (see link below)<br /><br /><a href="http://www.devx.com/dbzone/Article/42447"> <br />Keep Your SQL Server Indexes Fragmentation Free</a><br /><br />My DevX article was all about Sql Server 2005 and Sql Server 2008, but what about Sql Server 2000? surely you don't want to be stuck just running a performance monitor on your all your 2000 servers right? Here is the updated 2000 script to find all fragmented tables in 2000 along with the DBCC DBREINDEX wich will rebuild them for you. <br /><br />Catch-22: if the table has less than 1000 rows, a rebuild will not change anything and you may be left with having to drop and recreate the index manually anyways.<br /><br />The Code:<br />...I created a temp table manually to capture the results of SHOWCONTIG which I pipe to a grid result using the WITH TABLERESULTS hint, next this is joined against the information schema tables table. In the Where clause I specified that we are interested in 'Base Table's only so that we don't get a lot of system table specific stuff. I also added the cirteria that we want to see only fragmented tables of 5% and that have over 1000 rows. In my case my legacy databases are all well under 50gb and thus this query makes sense. This example use a cursor, as it's an old script, but I'll post up a cursor-less version as I prefer cursor-less solutions.<br /><br />As always watch those log files when you are defragging indexes!<br /><br /> <br />USE YourDatabaseName--Enter the name of the database you want to reindex<br /><br />CREATE TABLE #db_defrag <br />( ObjectName VARCHAR(255), <br />ObjectID INT, <br />IndexName VARCHAR(255),<br /> IndexID INT, <br /> [LEVEL] INT, <br /> Pages INT, <br /> [ROWS] BIGINT, <br /> MinRecordSize INT, <br /> MaximumRecordSize INT, <br /> AverageRecordSize INT, <br /> ForwardedRecords INT, <br /> Extents INT, <br /> ExtentSwitches INT,<br /> AverageFreeBytes DECIMAL(20,2), <br /> AveragePageDensity DECIMAL(20,2), <br /> ScanDensity DECIMAL(20,2), <br /> BestCount INT, <br /> ActualCount INT, <br /> LogicalFragmentation DECIMAL(20,2), <br /> ExtentFragmentation DECIMAL(20,2))<br /> <br />DECLARE @sql AS VARCHAR(100) <br /><br />SET @sql ='DBCC SHOWCONTIG WITH TABLERESULTS'<br /><br /> INSERT INTO #db_defrag <br /> EXEC (@sql)<br /> <br /> <br />DECLARE @TableName varchar(255)<br /><br />DECLARE TableCursor CURSOR FOR<br />SELECT table_name FROM information_schema.tables tbl<br />INNER JOIN #db_defrag dfrg ON tbl.table_name = dfrg.ObjectName <br />WHERE table_type = 'base table' AND dfrg.LogicalFragmentation >= 5 AND [ROWS] > 1000<br />ORDER BY LogicalFragmentation DESC <br /><br />OPEN TableCursor<br /><br />FETCH NEXT FROM TableCursor INTO @TableName<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /><br />DBCC DBREINDEX(@TableName,' ',90)<br />SET @sql = 'UPDATE STATISTICS ' + @TableName <br />EXEC (@Sql)<br /><br />FETCH NEXT FROM TableCursor INTO @TableName<br />END<br /><br />CLOSE TableCursor<br /><br />DEALLOCATE TableCursor<br /> <br />DROP TABLE #db_defrag<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com2tag:blogger.com,1999:blog-8619066.post-76495448268078395832009-07-21T09:47:00.000-07:002009-08-12T14:58:34.171-07:00UTO! Unidentified Table Object<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk3_l8PGmekOK8ddWMC7rtIYu-x9-cpk6XHMMgHGbtlDNNioTLkXVeBJIgxlRYxgJ0W4AqU13RaaPqUEeZQyiKOOSccqly0cIW8GUIT0bVEcKTEAmzB4PuS_VmiWMVFge5eX0t/s1600-h/UFO.jpg"><img style="float:left; margin:0 10px 10px 0;cursor:pointer; cursor:hand;width: 150px; height: 66px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk3_l8PGmekOK8ddWMC7rtIYu-x9-cpk6XHMMgHGbtlDNNioTLkXVeBJIgxlRYxgJ0W4AqU13RaaPqUEeZQyiKOOSccqly0cIW8GUIT0bVEcKTEAmzB4PuS_VmiWMVFge5eX0t/s200/UFO.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5369200070252976258" /></a><br />It's been a while since I've updated the blog, but I Did want to mention that I am working on a neat article, which focuses on my passion for performance. Stay tuned for the latest details...<br /><br />in the mean time, have you ever been stuck with someone else's database? Or how'bout a vendor database where someone needs you to extend a task. Well finding the stored procedures is relatively simple. Remember just go through profiler, run the process and you can monitor which stored procedures are called sometimes this also provides you some feedback on which tables are being accessed. Other times you may need to report on some of this information, so you may need to search the database on where they decided to store such information. I extended my own version of Narayana's searchalltables procedure, in this new version you'll notice that you get to also search text (and ntext) fields along with only a single while loop. <br /><br />Check out the latest script and article here:<br /><a href="http://www.devx.com/dbzone/Article/42340/0">Searching Unidentified Tables and Columns for Specific Content</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-71754840394074815672009-06-23T11:10:00.000-07:002009-08-12T15:01:07.971-07:00Mail Call!<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFgJ0lCEtRW8uZW14fo7GYp4cF8b_PXry0ZtaEqpmS5dqy6_vsE6kub1gazwQTn_tsByWQ3pWnFicJo6kRfjrZ2ci2x6EnYFK5-J6tkMwd-jfCjVDDp431PqN27dZulMP5joPm/s1600-h/Mail.jpg"><img style="float:left; margin:0 10px 10px 0;cursor:pointer; cursor:hand;width: 129px; height: 88px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFgJ0lCEtRW8uZW14fo7GYp4cF8b_PXry0ZtaEqpmS5dqy6_vsE6kub1gazwQTn_tsByWQ3pWnFicJo6kRfjrZ2ci2x6EnYFK5-J6tkMwd-jfCjVDDp431PqN27dZulMP5joPm/s320/Mail.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5369200729461534226" /></a><br /><span style="font-weight:bold;">E</span>mail, You use it, your colleges use it, even your systems use it. It's a part of everyday business. If you are a Sql Developer you have probably figured out how to implement email already, Often times I've seen many DBA's and Developers implement it from outside of SQL Server in rather ingenious ways...<br /><br />This article outlines how to setup Sql Server Mail in Sql Server 2000 and 2005 (2008 is the same as Sql Server 2005). By bringing mail inside of your server you can now send reports, alerts and other needed information based on the triggers and alerts that matter to you most. Making execution calls to xp_cmdshell to an opensource program (Blat) and sp_send_dbmail calls for Sql Server 2005 help leverage reporting from Sql Server.<br /><br /><a href="http://www.devx.com/dbzone/Article/42178">Sending E-mail from SQL Server 200X</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-18242275272637614742009-05-21T21:23:00.000-07:002009-05-21T21:28:42.693-07:00Limit your responses , please.When you are faced with request from users who will ask things like... i want to know the top 2 machines of every model type that have active leads, you may find yourself baffled and stunned to find that the Select TOP n does very little to help you out. The following article address the issue completely whether you're a sql developer or NOT. For myself it was a new look at existing solutions that we had employed all which were cumbersome and tedious to maintain, the solutions in the article describe the best approach which is easily extensible and flexible.<br /><a href="http://www.devx.com/dbzone/Article/41808"><span style="font-size:100%;">Limit Groups by Number Using Transact-SQL or MS Access</span></a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com1tag:blogger.com,1999:blog-8619066.post-25668205054024536902009-04-09T11:37:00.000-07:002009-04-09T11:48:50.940-07:00MSDE enable TCP/IP or Named Pipes<span style="font-size: 10pt; font-family: arial;"><o:p><span class="titles1"><span style="font-size: 10pt;"><p style="margin: 0in 0in 0pt;"><strong><span style="font-weight: normal;">When you inherit a new server sometimes you find that you can't connect to the server, to fix that you may need to simply enable the protocol via Sql Server Network Utility (svrnetcn) that is listening For MSDE.</span></strong></p></span></span></o:p></span><ol><li><p class="MsoNormal" style="margin: 0in 0in 0pt;"><span style="font-size: 10pt; font-family: Arial;">In Windows, click <strong>Start</strong> and <strong>Run</strong>.</span></p> </li><li> <p class="MsoNormal" style="margin: 0in 0in 0pt;"><span style="font-size: 10pt; font-family: Arial;">Enter <strong>svrnetcn</strong> and click <strong>OK</strong>.</span></p> </li><li><span style="font-size: 10pt; font-family: Arial;"><span style="font-size: 10pt; font-family: Arial;">Under the <strong>General tab</strong>, verify that the correct instance for the server is displayed in the <strong>Instance(s) on this server</strong> box.</span></span> </li><li><span style="font-size: 10pt; font-family: Arial;">Highlight your desired protocol and click <strong>Enable</strong> (double clicking the name also moves the protocol to the enabled protocols box).</span> </li><li><span style="font-size: 10pt; font-family: Arial;">Click <strong>OK</strong>.</span></li><li><span style="font-size: 10pt; font-family: Arial;">Restart the Sql Server Instance</span></li><li><span style="font-size: 10pt; font-family: Arial;">In Windows, click <strong>Start</strong> and <strong>Run</strong>.</span></li><li><span style="font-size: 10pt; font-family: Arial;">enter services.msc</span></li><li><span style="font-size: 10pt; font-family: Arial;">Locate the MSSQLSERVER instance you modified in the Sql Server Network Utility and Restart the service.</span></li></ol><span style="font-size:85%;"><span style="font-family: arial;">You may wish to ensure that your users are not logged on or at least notified of this change as it will kick them out of the application</span></span><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-71950207708087153402009-03-17T22:04:00.000-07:002009-03-17T22:10:09.382-07:00SSIS SOS!If you have been regretting making the move from Sql Server 2000 to Sql Server 2005, you will be in complete shock and awe when you find that you no longer have DTS to move your data around. The good news is that in Sql Server 2005 you get Sql Server Integration Services (SSIS) a full feature ETL product for Sql Server 2005. If you want to move things moving quickly take a look at the <a href="http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm">Sql Server 2005 Import / Export Wizard</a> short article which outlines just how easy shuffling your data around can really be.<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-43863789716081736822009-03-17T11:56:00.000-07:002009-03-17T11:58:12.411-07:00Comma delimited ListsTake the post about finding columns and incorporate it with this post from a college of mine. <br /><a href="http://www.artfulsoftware.com/infotree/tip.php?id=753">http://www.artfulsoftware.com/infotree/tip.php?id=753</a><br /><br />Now you're off and running turning lists to comma lists, in corporate that with the list table function I posted sometime back, and you can go backwards and forwards!<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-7585744198797441322009-03-17T11:43:00.000-07:002009-03-17T11:47:56.596-07:00Find me a column...Forgive the fact that the following SQL is not <span style="font-style: italic;">formatted</span>, but this topic came up today on a list that I frequent, which was how to get all the fieldnames of a view (or table etc). this dandy little select statement will gather just that and all you need to really parse it for is your table name or column name... maybe you want to know how many times you named something "EntryTime" this is an easy way to find all that information at a glance.<br /><br />This is a Sql Server 2005 version <span style="font-size:78%;">(If you need it I can post a Sql Server 2000 version as well.)</span><br /><br />SELECT t.name AS TableName,<br />sc.name AS SchemaName,<br />c.name AS ColumnName,<br />c.column_id AS ColumnID,<br />c.precision AS [Precision],<br />types.name AS TypeName,<br />basetypes.name AS BaseTypeName,<br />st.name AS TypeSchemaName,<br />CASE WHEN c.max_length>=0 AND basetypes.name IN (N'nchar', N'nvarchar') THEN c.max_length/2 ELSE c.max_length END AS Length,<br />c.scale AS Scale,<br />CONVERT(bit, c.is_identity) AS [Identity],<br />CONVERT(bit, c.is_computed) AS [IsComputed],<br />CONVERT(bit, ISNULL(ic.is_not_for_replication,0)) AS NotForReplication,<br />CAST(ISNULL(ic.seed_value,0) AS DECIMAL(38)) AS IdentitySeed,<br />CAST(ISNULL(ic.last_value,0) AS DECIMAL(38)) AS IdentityCurrent,<br />ISNULL(ic.increment_value,0) AS IdentityIncrement,<br />CONVERT(bit, c.is_nullable) AS AllowNulls,<br />c.default_object_id AS DefaultTextID,<br />ds.name AS DefaultName,<br />c.default_object_id AS defaultid,<br />dsc.name AS DefaultSchemaName,<br />CONVERT(bit, CASE ds.parent_object_id WHEN 0 THEN 1 ELSE 0 END) AS IsBoundDefault,<br />rs.name AS RuleName,<br />rsc.name AS RuleSchemaName,<br />CONVERT(bit, CASE WHEN fi.language_id IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed,<br />cc.definition AS ComputedText,<br />c.is_rowguidcol AS IsRowGuidCol,<br />c.collation_name AS Collation,<br />fi.language_id AS FullTextLanguage,<br />fisc.name AS FullTextTypeColumn,<br />c.is_xml_document AS XMLDocument,<br />xscs.name AS XMLCollection,<br />xsc.name AS XMLCollectionSchemaName,<br />t.type AS type,<br />CONVERT(bit, ISNULL(cc.is_persisted, 0)) AS [IsPersisted]<br />FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_id<br />LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id<br />LEFT JOIN sys.types basetypes WITH (NOLOCK) ON c.system_type_id=basetypes.system_type_id AND basetypes.system_type_id=basetypes.user_type_id<br />LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id<br />LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id<br />LEFT JOIN sys.identity_columns ic WITH (NOLOCK) ON ic.object_id=c.object_id AND ic.column_id=c.column_id<br />LEFT JOIN sys.all_objects ds WITH (NOLOCK) ON ds.object_id=c.default_object_id<br />LEFT JOIN sys.all_objects rs WITH (NOLOCK) ON rs.object_id=c.rule_object_id<br />LEFT JOIN sys.fulltext_index_columns fi WITH (NOLOCK) ON fi.object_id=c.object_id AND fi.column_id=c.column_id<br />LEFT JOIN sys.all_columns fisc WITH (NOLOCK) ON fi.type_column_id = fisc.column_id AND fisc.object_id=c.object_id<br />LEFT JOIN sys.computed_columns cc WITH (NOLOCK) ON cc.object_id=c.object_id AND cc.column_id=c.column_id<br />LEFT JOIN sys.schemas dsc WITH (NOLOCK) ON dsc.schema_id=ds.schema_id<br />LEFT JOIN sys.schemas rsc WITH (NOLOCK) ON rsc.schema_id=rs.schema_id<br />LEFT JOIN sys.xml_schema_collections xscs WITH (NOLOCK) ON xscs.xml_collection_id=c.xml_collection_id<br />LEFT JOIN sys.schemas xsc WITH (NOLOCK) ON xsc.schema_id=xscs.schema_id<br />WHERE t.type IN ('U', 'FT', 'TF', 'IF', 'S', 'TT', 'V')<br />ORDER BY c.object_id, c.column_id<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-4659308630252791382009-02-13T08:17:00.001-08:002009-02-13T08:30:40.700-08:00Turbo Charge Your SSMSThe other day a co-worker mentioned one of his biggest pet peeves about Sql's new IDE. Of course he likes the old IDE, but what he detested the most was the slow bloat time it took to get his SSMS loaded on the screen. When we timed the boot time for his Enterprise Manager to load it took nearly 7 seconds. Not bad, but then proceeded to tell me how much longer SSMS (Sql Server Management Studio) took to load. I guessed that it could possibly be about 10 seconds (only 3 more seconds) but I was shocked to see it load in nearly 25 seconds. it was nearly 3x less productive just loading the thing before you could connect to do anything. I couldn't believe that it was nearly that slow on his computer, I had never timed it on my pc. So when I returned I had to try it. Again the load time was nearly 20 seconds on my machine (about 18 seconds to be exact). I was not entirely pleased with my results but thankfully Google provided the answer, one quick search for "Turbo charge my SSMS" yielded the following result which brings that load time to that of Enterprise Manager about 6-7 when you are prompted with the server you want to connect to.<br /><br /><a href="http://www.sql-server-performance.com/faq/sql_server_management_studio_load_time_p1.aspx">Speed up your Sql Server Management studio </a><br /><br />The solution is rather simple. First get rid of the splash screen (unless you really like it.) by adding the /nosplash parameter to your shortcut.<br /><br />next the real jewel, Go into Internet Explorer's Tools>Options>Advance Tab and look for the Security section, uncheck the following two settings:<br /><br />o Check for publisher's certificate revocation<br />o Check for server certificate revocation*<br /><br />once you have completed these tasks you will notice what appears to be a TurboCharged SSMS!<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com1tag:blogger.com,1999:blog-8619066.post-52895673303504975352008-12-03T14:05:00.000-08:002008-12-03T15:35:27.820-08:00One sandwich short of a picnicI had an issue at work the other day, something that seems kind of odd to me. You see in Sql Server 2000 you could setup a view with an order by. Of course this required a TOP 100 PERCENT to be specified at the top of the query. You could then take that view and base new views off of it and all results would be ordered by the original view. In Sql Server 2005 (SP1 or even SP2) everything changed, now you could no longer get the correct order by if you coupled it w/ the TOP 100 PERCENT in your view, but then again, if you do not use a the TOP keyword you can't save your view. The solution to how to get around the issue is in the following blog article.<br /><br /><a href="http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html">http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html</a><br /><br />A proposed solution has been placed on the MSDN site so you can vote on it. It seems the proposal is to do away with the ability to create ordered by views which technically go against the rules of rational theory....<br /><br /><a href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249248">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249248</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-86545746149662230612008-06-06T10:55:00.000-07:002008-06-06T11:02:46.668-07:00Watch those serversIf you're an active DBA, you'll find that your mornings are spent reviewing failed jobs and checking to see what else needs watching. At my company we were fortunate to purchase Idera's Diagnostic Manager. It is a good tool for reviewing your databases at a quick glance and identify any areas that need attention, ie. Space constratins, failed jobs and other critical processes. <br /><br />Not everyone has the ability to go out and spend thousands of dollars on a monitoring solution. That's why I really liked the following article by David Bird over at Sql Server Central (one of my favorite Sql Sites) titled <a href="http://www.sqlservercentral.com/articles/Administration/63042/"><span style="font-size:100%;">SQL Overview IV - DBA's Morning Review</span></a> (http://www.sqlservercentral.com/articles/Administration/63042/). In it he describes a set of reports that you can use to review your servers quickly during the morning and spend more time fixing problems than reviewing things. I Plan to implement many of these solutions in my own environment and see how well it stacks up to our paid solution. I will report back and let you all know!<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-10265156963014307352008-06-06T10:34:00.000-07:002012-07-20T07:42:37.297-07:00Change your Recovery ModelOn occasion you may be tasked with switching your recovery model for a database. This task is very trivial and you may already know how to do this via Sql Server Enterprise Manager or even Management Studio. But you can do this via Tsql as well (as with all things sql server related)<br />
<br />
The syntax is basically<br />
ALTER DATABASE Northwinds SET RECOVERY SIMPLE<br />
<br />
The recovery Models available are, Simple, BulkLogged and Full.<br />
<br />
The Simple Recovery model is as you guessed the basic recovery model which pretty much allows you to recover your database from a full backup. The simple recovery model is ideal for test and development databases where data loss is not generally a concern. You may wish never to use this model in a production environment unless the business is willing to loose up all data changes since the last Full or Differential backup. In this model you do not have to maintain the Transaction log as nothing is generally written to it.<br />
<br />
The BulkLogged Recovery model will log every event except those that are Bulk transactons. This has almost the same recoverability as a Full Recovery model. With this recovery model you can do full, differential and log backups, you are also capable of restoring up to the minute so long as your last change was not a bulk logged operation, in which case those changes will remain lost. You will need to schedule a regular backup for your transaction log for those operations that are not Bulk transactions.<br />
<br />
The Full Recovery model offers the highest amount of protection against data loss. This model allows you to recover any piece of data up to the minute (prividing you have the last backup). You can do full, differential and log backups. You can restore any of the backups for returning to your point in time. Such as a FULL backup followed by a Differential and finally your set of Log backups until the point in time when the failure occurred. In this recovery model you will want to schedule a regular log backup for your transaction, otherwise your database log files will grow and grow.<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-5545020034368173432008-03-07T13:08:00.000-08:002008-03-07T13:15:41.836-08:00Defrag or don't defrag that is the question!Back in 2005 I wrote a quick 'n' dirty post (really) about how to maintain indexes. That applied to Sql Server 2000 well enough but did not even touch what was happening with Sql Server 2005. Instead of writing a separate article on 2005, which now come to think about it, I may want to. I instead opted to update my older post, and it now reads more like an article. I've included the link below so that it's quick / easier to find. In this article I also posted up my own defrag monitor script, which should be helpful in locating defraged indexes and that way you can easily defragment only the ones that matter.<br /><br />Good Luck!<br /><a href="http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html">http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html</a><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-58182532107350786812007-12-27T10:17:00.000-08:002007-12-27T10:22:16.337-08:00How many rows are there?This question comes up quite a bit when DBAs are faced with space issues... the simple solution most people say is to do a quick SELECT COUNT(*) FROM <tablename>, but that ends up being such a costly solution and heavy I/O cost if you have a lot of tables or a lot of data.<br /><br />Fortunately you can obtain the number of rows without having to run such a heave I/O intensive operation.<br /><br />In Sql Server 2000 the command looks like this:<br />Select OBJECT_NAME(ID), rows From sysindexes Where id = OBJECT_ID(<tablename><tablename>TableName) AND indid < 2<br /><br />While I found that the number of rows will differ slightly it is close enough to make needed judgment calls based on row count. (I.E. defragging, partitioning, archiving, etc.)<br /><br />In Sql Server 2005 the command looks like this: SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) = <tablename><tablename>TableName<br /><br />note, be sure to replace TableName with your name in order to locate the rowcount for the individual table. You can alternatively modify the Where clause to be by Rows > x so you can find tables over a specified number of rows. Also, if there are more than one index you may end up yielding several responses as the entries in both the partition table and the sysindexes table count the rows in ... you guessed it the index.<br /></tablename></tablename></tablename></tablename></tablename><div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0tag:blogger.com,1999:blog-8619066.post-21446151998027758902007-12-21T12:09:00.000-08:002007-12-21T12:09:01.421-08:00We don't care about no stinking buffersWhile you work on your newest indexes / table design it is often necessary to clean up the cache on Sql Server to ensure that your changes are actually making a difference. <br /><br />Typically to clear the cache you'll want to run, DBCC DROPCLEANBUFFERS.<br /><br />If you are also executing your test via sprocs you'll want to clear that cache as well to do that just run, DBCC FREEPROCCACHE.<br /><br />This ensures that you are always starting from the same point at each run. If you do not clear the cache between tests you can't be sure that your time results are accurate.<div class="blogger-post-footer">TSQL scripts and more...</div>Franciscohttp://www.blogger.com/profile/07407021734922699043noreply@blogger.com0