Contents tagged with SQL server
-
Ten Caching Mistakes that Break your App
-
Dropthings now available from Microsoft/Web
Dropthings is now available on Microsoft/Web. You can now install it using the Web Platform Installer. I will soon write an article how to make an installer that can install a ASP.NET website, a SQL Server database, setup web.config files, setup directory permissions (eg App_Data) etc. It wasn’t straightforward and I learnt some best practices from the Microsoft/Web team. But for now, go ahead and download the app and build cool sites out of it.
-
Building High Performance Queue in Database for storing Orders, Notifications, Tasks
We have Queues everywhere. There are queues for asynchronously sending notifications like email and SMS in most websites. E-Commerce sites have queues for storing orders, processing and dispatching them. Factory Assembly line automation systems have queues for running tasks in parallel, in a certain order. Queue is a widely used data structure that sometimes have to be created in a database instead of using specialized queue technologies like MSMQ. Running a high performance and highly scalable queue using database technologies is a big challenge and it’s hard to maintain when the queue starts to get millions of rows queued and dequeued per day. Let me show you some common design mistakes made in designing Queue-like tables and how to get maximum performance and scalability from a queue implemented using simple database features.
-
Exporting normalized relational data from database to flat file format
Sometimes you need to export relational normalized data into flat files where a single row comes from various tables. For example, say you want to export all customer records along with their work and home address, and primary phone number in a single row. But the address and contact information are coming from different tables and there can be multiple rows in those table for a single customer. Sometimes there can be no row available in address/phone table for a customer. In such a case, neither INNER JOIN, nor LEFT JOIN/OUTER JOIN will work. How do you do it?
-
Memory Leak with delegates and workflow foundation
Recently after Load Testing my open source project Dropthings, I encountered a lot of memory leak. I found lots of Workflow Instances and Linq Entities were left in memory and never collected. After profiling the web application using .NET Memory Profiler, it showed the real picture:
-
Optimize ASP.NET Membership Stored Procedures for greater speed and scalability
Last year at Pageflakes, when we were getting millions of hits per day, we were having query timeout due to lock timeout and Transaction Deadlock errors. These locks were produced from
aspnet_Usersandaspnet_Membershiptables. Since both of these tables are very high read (almost every request causes a read on these tables) and high write (every anonymous visit creates a row onaspnet_Users), there were just way too many locks created on these tables per second. SQL Counters showed thousands of locks per second being created. Moreover, we had queries that would select thousands of rows from these tables frequently and thus produced more locks for longer period, forcing other queries to timeout and thus throw errors on the website. -
Linq to Sql solve transaction deadlock and query timeout problems using uncommitted reads
When your database tables start accumulating thousands of rows and many users start working on the same table concurrently, SELECT queries on the tables start producing lock contentions and transaction deadlocks. This is a common problem in any high volume website. As soon as you start getting several concurrent users hitting your website that results in SELECT queries on some large table like aspnet_users table that are also being updated very frequently, you end up having one of these errors:
-
99.99% available ASP.NET and SQL Server Production Architecture for SaaS
You have a hot ASP.NET+SQL Server product, growing at thousand users per day and you have hit the limit of your own garage hosting capability. Now that you have enough VC money in your pocket, you are planning to go out and host on some real hosting facility, maybe a colocation or managed hosting. So, you are thinking, how to design a physical architecture that will ensure performance, scalability, security and availability of your product? How can you achieve four-nine (99.99%) availability? How do you securely let your development team connect to production servers? How do you choose the right hardware for web and database server? Should you use Storage Area Network (SAN) or just local disks on RAID? How do you securely connect your office computers to production environment?
-
10 ASP.NET Performance and Scalability Secrets
-
A significant part of sql server process memory has been paged out. This may result in performance degradation
If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently encounter this problem where SQL Server says: