Brent Ozar Unlimiteds specialized experts focus on your goals, diagnose your tough database pains, and make Microsoft SQL Server faster and more reliable. Sql Trigger Check Field Updated 2018' title='Sql Trigger Check Field Updated 2018' />Things Developers Should Know About SQL Server. Hi.  Im a former developer whos moved into database administration, and heres what I wish somebody would have told me when I got started. SQL functions rarely perform well. Good developers like to reuse code by putting it into functions, and then calling those functions from multiple places. Thats a great practice in the app tier, but it has huge performance drawbacks in the database tier. Check out Paul Whites excellent post on Forcing a Parallel Query Plan in particular, the list of things that produce a serial zone in the plan. Most functions will cause your query to go single threaded. Dynamics GP Inventory SQL Views. Customer Pricing Customer specific pricing for each item. Inventory Price Levels All price levels for all items. An Open Source C web crawler with Lucene. NET search using SQL Server 2008201220142016CE An Open Source C web crawler with Lucene. NET search using MongoDB. Sad trombone. If you do want to reuse code, consider stored procedures and views instead. Granted, they can come with their own performance drawbacks, but Im just trying to get you started on the right foot as quickly as possible here, and functions are a broken foot. WITH NOLOCK doesnt actually mean no locking. At some point in your career, youre going to start using WITH NOLOCK on everything because it gets your query results faster. Thats not necessarily a bad idea, but it can come with some surprising side effects. MODIFYALLbe3.png?w=610' alt='Sql Trigger Check Field Updated 2018' title='Sql Trigger Check Field Updated 2018' />When you query a table even WITH NOLOCK you take out a schema stability lock. No one else can change that table or indexes until your query is finished. That doesnt sound like a big deal until you need to drop an index, but you cant because people are constantly querying a table, and they think theres no overhead as long as they use WITH NOLOCK. Theres no silver bullet here, but start by reading about SQL Servers isolation levels I bet READ COMMITTED SNAPSHOT ISOLATION is an even better choice for your app. It gets you consistent data with less blocking hassles. Use 3 connection strings in your app. I know, youve only got one SQL Server today, but trust me, this is worth it. Set up three connection strings that all point to the same destination today, but down the road, when you need to scale, youll be able to set up different database servers to handle each of these Connection for Writes Realtime Reads this is the connection string youre already using today, and you think that all data needs to come from here. You can leave all of your code in place, but as you write new code or touch existing pages, think about changing each query to one of the below connections. Connection for Data 5 1. Minutes Old this is for data that can be slightly stale, but still needs to be from today. Connection for Data as of Yesterday for management reports and trending data. If you run an online store, you might pull reviews from here, for example, and tell users that their reviews take a day to publish. That first connection string is the toughest one to scale we dont have a lot of options in SQL Server to scale out multiple servers that handle writes. We do have options theyre just painful to implement and manage. The lower tier connection strings 2 and 3 are much, much easier and cheaper to scale. For more about this technique, check out my 3 Favorite Connection String Tips. Use a stagingapptempdb database. Your app probably uses the database for some scratch work processing, sorting, loading, caching, etc. It wouldnt break your heart if this data disappeared, but youd like to keep the table structures around permanently. Today, youre doing this work in your main application database. Create a separate database call it My. App. Temp and do your work in there instead. Put this database in simple recovery mode, and only back it up once daily. Dont hassle with high availability or disaster recovery on this database. This technique accomplishes a lot of really cool scalability stuff. It minimizes the changes to the main app database, which means you get faster transaction log backups and differential backups for it. If youre log shipping this database to a disaster recovery site, your important data will arrive faster and not be impeded by all the scratch work. You can even use different storage for these different databases perhaps cheap local SSD for My. App. Temp, keeping your shared storage connection free for the critical production stuff. Yesterdays articles and books are often wrong today. Team Building Games Rope. SQL Server has been out for over a decade, and a lot has changed over the years. Unfortunately, the old material isnt updated to cover whats happening today. Even todays material from reputable sources is often wrong take this critique of Microsofts Performance Tuning SQL Server guide. Fellow Microsoft Certified Master Jonathan Kehayias points out a bunch of really bad advice that comes straight from a Microsoft document. When you read something that sounds like good advice, I like to try the Anti Doctor Phil strategy. Dr. Phil preaches that you should love every idea for fifteen minutes. Instead, try hating it try to disprove what you read before you put it into production. Even when advice is generally good, it might not be good advice for your own environment. Yes, that includes my advice too. Avoid ORDER BY sort in the app instead. To sort your query results, SQL Server burns CPU time. SQL Server Enterprise Edition goes for about 7,0. CPU core not per processor, but per core. A two socket, 6 core each server rings up at around 8. You can buy a heck of a lot of application servers even ones with 2. GB or more of memory for 8. Consume all of the query results as fast as possible into memory in your app, and then sort. Your application is already designed in a way that you can scale out multiple app servers to distribute CPU load, whereas your database serveris not. UPDATE Ive gotten a lot of comments wailing about how the app only needs ten rows of a ten million row dataset. Sure, if youre doing TOP 1. And if the data sounds like too much for the app server to sort, its probably causing work on the SQL Server too. We talk about how to find those queries in the webcast listed at the bottom of this post. Also, keep in mind that I said Avoid ORDER BY, not Never use ORDER BY. I use ORDER BY myself too but if I can avoid that work in the very expensive data tier, Ill avoid it. Thats what avoid means. This part here is where the My. SQL and Postgre. SQL guys start screaming about how you can avoid licensing costs altogether with open source databases. This part here is where you would expect me to have a witty retort, but I dont. If youre building a brand new app and youre choosing a database, read my Stack. Overflow answer on which database handles the most load. SQL Server has built in zero impact instrumentation tools. SQL Servers dynamic management views DMVs can tell you all kinds of killer stuff like Which SQL statements are causing the most load on your server. Which indexes are wasting space and slowing down insertsupdatesdeletes. How fast storage is responding to requests on a database by database level and even more fine grained than thatWhere your servers bottleneck is, like CPU, disk, network, locking, etc. All you have to know is where to look, and well show you. Wanna learn some new tricks Check out our T SQL Level Up online class we guarantee its the best T SQL training trailer youve ever seen, and its free Start our T SQL course now.