AppDev: Something You Should Know by Irena Kennedy

Everything that is related to application development, and other cool stuff...

Browse by Tags

Tagged Content List
  • Blog Post: SQL Server Connection Leak Simulation Tool

    Sometimes it’s necessary to test application performance in low available resource situations… I’ve seen tools that simulate low available memory, do network throttling, etc., but I have not come across one that simulates a lot of SQL Server connections being in use… Attached is such a tool. I...
  • Blog Post: SYSK 360: What’s taking up the space in my database? Also, a word about SQL reports…

    So, you’ve updated usage stats on your database by running EXEC sp_spaceused @updateusage = N 'TRUE' But, you still can’t figure out why it’s reporting much more used space then the data that appears in your tables… Try running the following query to get number of rows and detailed space...
  • Blog Post: SYSK 359: How to Purge Workflow Tracking Data Inserted By SqlTrackingService

    To my great surprise, after doing a search for a script that would delete the events logged by the almighty SqlTrackingService service, I didn’t find one… So, I quickly created it (see below), and it seems to do the job just fine given the current table schema implementation used by the service: IF...
  • Blog Post: SYSK 357: SQL Server Login Problem on Fresh Installation in Vista

    I just installed SQL Server 2008 CTP (Katmai) and all the patches on my Vista laptop… However, trying to login using SQL Management Studio or osql using integrated security (and, yes, I’m an admin on my box) results in the “login failed” error. To resolve the problem, I had to turn off the “User Control...
  • Blog Post: SYSK 349: What’s New in SQL 2008 (code named Katmai)?

    Here are some of the new and improved features (extracted from David Campbell’s presentation) that will be available in SQL 2008: - Transparent data encryption - External key management - Data auditing - Pluggable CPU - Transparent failover for data mirroring - Declarative management...
  • Blog Post: SYSK 347: How-To Keep Historical/Audit Data Accurate

    I’m sure you’ve heard this saying many times before – “the hard disk space is cheap now – save everything”… With that, many companies create paired (or buddy) tables for all transactional data, i.e. for every transactional table (i.e. not lookups, a.k.a. domain values) you’d create another one with same...
  • Blog Post: SYSK 345: How To Assure Correct LastUpdatedDateTime Value in Your SQL Tables

    Often, developers add LastUpdatedDataTime (or LastUpdatedTimestamp) and LastUpdatedByUser columns to tables in relational databases. And, as one might guess, these fields are often created to be not nullable with default values set by using SQL functions – getdate() and suser_name() – correspondingly...
  • Blog Post: SYSK 338: What SQL Queries Are Currently Running?

    That’s easy -- just run the SQL statement below… select session_id , request_id , start_time , status , cpu_time , er . total_elapsed_time , substring ( st . text , ( er . statement_start_offset / 2 ) + 1 , (( case er . statement_end_offset when - 1 then datalength ( st . text ) else er...
  • Blog Post: SYSK 333: What Query Plans Are Cached in SQL Server?

    It’s well known that, to improve performance, SQL Server caches query execution plans in memory. But would you like to know what query execution plans are currently in the cache? The following query will yield that information: select substring ( st . text , ( qs . statement_start_offset...
  • Blog Post: SYSK 328: The “Hidden” System Database in SQL 2005

    If you just open SQL Server Management Studio and expand the System Databases node, you’ll see four databases – master, model, msdb and tempdb (just like in SQL 2000). However, SQL Server 2005 has a new system database called the ‘Resource’ database, which contains all the system objects (see select...
  • Blog Post: SYSK 312: Why You Should Not Change SQL Server Service Account Using Services Management Console

    The SQL Server documentation states that it’s strongly recommended to use SQL Server Configuration Manager and not Control Panel\Administrative Tools\Services (services.msc) when changing SQL Server or SQL Server Agent service account. The question is – why? The reason is that when SQL Server service...
  • Blog Post: SYSK 300: Why TABLESAMPLE Is Not Returning Specified Number of Rows

    One of the new features in SQL 2005 is the clause that allows you to get a random set of rows. The syntax supports specifying either a percentage or a number of rows to return: TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] ) [ REPEATABLE (repeat_seed) ] For example, using AdventureWorks...
  • Blog Post: SYSK 295: Difference between password handling in SQL 2000 and SQL 2005

    Did you know that in SQL Server 2000, the engine used to maintain two copies/versions of each SQL Server login password. One contained the actual password supplied by the user, and the second one was the password converted by SQL Server to all uppercase letters. This was done to speed up case-insensitive...
  • Blog Post: SYSK 291: SQL 2005 SP 2 is Available and Comes with Good News About Licensing Costs

    With SQL Server 2005 SP2 released to the web on Feb. 19, 2007, SQL Server Enterprise Edition customers, and I quote, “only need to purchase one license per physical processor regardless of the number of virtually deployed instances. “ In other words, “w hen a customer licenses all of the processors...
  • Blog Post: SYSK 287: Custom C#/VB (CLR) Types Used in SQL Server

    Did you know that you can use custom CLR types in Microsoft SQL Server by adding [ Serializable , Microsoft.SqlServer.Server. SqlUserDefinedType ] attribute? And, yes, you can implement and use methods – see Microsoft.SqlServer.Server. SqlMethod attribute… Here are some good links for more information...
  • Blog Post: SYSK 279: What’s New in the World of Database Tuning?

    Did you know that SQL 2005 Database Engine Tuning Advisor (replacement for SQL 2000 Index Tuning Wizard) can do its analysis using production server’s hardware configuration information, metadata and statistics, but perform the actual tuning on a test server? And it does this without copying the actual...
  • Blog Post: SYSK 275: Pagination in SQL – Easy!

    Unfortunately, it’s still a common case that the pagination logic resides in UI… Why I say ‘unfortunately’? Because by making this design choice, you have: Used up bandwidth transmitting the data that may never be seen by the end user. Potentially, are not achieving best performance & scalability...
  • Blog Post: SYSK 263: .NET Stored Procs Do Not Execute in Parallel using MARS

    The http://msdn2.microsoft.com/en-us/library/ms345109.aspx article has the following note in the Interleaved Execution section: “Stored Procedures written in any of the .Net languages will not interleave while managed code is executing. If the inproc data provider is used, then the executed batch...
  • Blog Post: SYSK 261: Investigating Blocked Processes in SQL Server

    Imagine, you execute who_is_blocked stored procedure and get a list of the blocked resources, the databases involved, the requesting and the blocking session ids, and the request mode. With that information, you can choose to terminate the blocking session, or otherwise handle the issue… Thanks to T...
  • Blog Post: SYSK 257: .NET way to analyze SQL data I/O performance

    Would you like to know how many server round trips it took to retrieve your data using ADO.NET? Would about the number of bytes sent and received? Total connection time? Command execution time? Number of returned rows? Welcome to the System.Data.SqlClient.SqlStatistics class. While the class itself...
  • Blog Post: SYSK 253: SQL: version, platform info and then some…

    Say, you need to quickly find out the version of SQL server and the operating system running on a computer. You could, execute ver from command prompt to get the version of Windows operating system, e.g. Microsoft Windows [Version 6.0.6000] . Then, to get the SQL Server version, you could run select...
  • Blog Post: SYSK 252: Running SQL statements at a desired time

    Say, you need to run a certain stored proc or some T-SQL commands, but you must do it “after normal hours”… You could sit and wait until the time you are allowed to hit that F5 (run) key in Query Analyzer, or you could create and schedule a SQL Agent job… But if it’s a onetime thing, you could also use...
  • Blog Post: SYSK 236: Using SQL Server Notification Services API Remotely

    Did you notice that all samples (at least all I’ve come across) show how to use Notification Services interface running locally, i.e. on the SQL Server computer? The code snippet below shows getting a list of subscribers: Microsoft.SqlServer.NotificationServices. NSInstance ns = new Microsoft...
  • Blog Post: SYSK 228: Get Table Columns or Rows with Single Key Press

    Imagine this: you type in a table name in SQL Server Management Studio, press Ctrl+9 and get all rows from that table displayed in the results pane. Or you press Ctrl+8 and instead of data rows, you get metadata about columns of that table. No more typing of ‘SELECT * FROM MyTable’ over and over...
  • Blog Post: SYSK 220: Triggers and Bulk Insert of Data

    Fact: triggers execute automatically whenever user modifies data in the underlying table or view. In my projects, I frequently use triggers when comprehensive auditing is a requirement. However, I’ve seen some developers use triggers as a mechanism to kick off a workflow of sort, e.g. send an e-mail...
Page 1 of 3 (56 items) 123