A small SQL Server DBA wants to talk about the capabilities of SQL Server

A small SQL Server DBA wants to talk about the capabilities of SQL Server

Baidu has not yet searched for relevant individuals to write more valuable articles, at least in the Chinese network of the world there is no

But there is an article on Microsoft's website: "Comparing SQL Server with IBM DB2"

The article compares from the following aspects

1. TCO and ROI
2. Performance and Scalability
3. High Availability
4. Security
5. Management
6. Development efficiency
7. Business Intelligence and data warehousing
8. OLTP
9. SAP Integration

This article is very good

Performance and Scalability

SQL Server has better performance and scalability than IBM DB2.

• The benchmark shows that SQL Server can handle large workloads:

  • In the TPC-C benchmark, SQL Server has more than 1 million transactions per minute and has the best performance on the Windows platform.
  • Of the 10 comparisons in the Tpc-h benchmark, 7 SQL Servers are better than DB2.
  • Supports 93,000 concurrent users in a SAP-SD certification benchmark, which is 10 times times more than the largest SAP customer in the world.
  • SQL Server is the undisputed leader in the new OLTP benchmark standard-TPC-E.
  • Read the latest SQL Server 2008 benchmark results.

  
  

• SQL Server 2008 includes up-to-date performance and scalability, better than DB2 to provide greater performance for large workloads:

  • o Improve partition tables, indexes, scopes, and more
  • o compression support meets or exceeds IBM DB2 UDB Resource management to control finer CPU and memory resources
  • O Performance data collectors can locate issues, tune and monitor instances of SQL Server across the Enterprise
  • O Highly tuned ETL engine currently maintains world ETL performance record

Success Stories

• Citi Group runs Lava market montage solution runs on SQL Server, up to 200,000 times per second update frequency
• Xerox uses SQL Server to manage 7 million of transactions per day, up to 99.999% uptime
• American Power Conversion migration to SQL Server Enterprise---achieves 100% faster response time with a cost savings of $800,000
• The VHA migration from the DB2 mainframe to SQL server-increased 25% efficiency, reduced replication time by 89%, and significantly reduced total cost of ownership.
• Countrywide Home Loans Select SQL Server to improve performance for high availability
• Microsoft IT uses SQL Server to drive 27TB of global statutory security tools
• Nasdaq, the world's leading technology trading, real-time ticketing system that uses SQL Server to handle up to 5000 transactions per second.
• Unisys UPSS System, a data warehouse that is architected on SQL Server, has entered the TOP10 ranks of the data Warehouse peak load.
• Premier Bankcard upgrades the Data warehouse and OLTP databases to SQL Server for a few terabytes.
• Danske Supermarket runs 600GB of multidimensional analytics on SQL Server Analytics services, and 10TB of business intelligence data

There is also a post, "SQL Server concurrency processing power in the end?" What do you say, master? 》

In fact, this topic is too open ended, whether people think it is located under Oracle, or only suitable for small and medium-sized databases, I just want to talk about its ability in my eyes

I believe that after I finished writing other database enthusiasts will come out to spray,Oracle ,MySQL , after all, this article with the "C # and Java which strong" and other articles are more similar.

I'm talking about two of the most care-focused aspects of this article: data volume, performance, and functionality

In fact, we have just started to use the database is the most concerned about the performance of the OLTP scene and the database system can hold the amount of data

First, hold the large amount of data

As a mature business database, SQL Server is no problem with holding large data volumes.

As mentioned in the previous article:Microsoft IT uses SQL Server to drive 27TB of global statutory security tools

In my company also have some relatively large database, database volume also has 7~8TB, small dozens of MB

Many people say: "SQL Server can't handle massive data, the amount of data a large SQL Server can not handle!" ”

I would like to ask: "How much data does the mass of data have?" 100 million? 1 billion? 10 billion? 1TB? 10TB? 100TB? ”

Anyway, I've been looking at so much data every day (there are also up to 1 billion on the previous TB), or you can manage them with SQL Server.

I would like to explain how to compare the amount of data: the volume of The comparison data should be compared with the actual size of the database, and should not be compared with the size of the single-table data volume!

Here is an example: before our database server has a table, there are six fields, all of the int type, the amount of single table data is 100 million +, but the size of the database is only 20G

In my eyes is just a relatively small database, although its data volume is more amazing

In the park Friend Horse Non-code blog has written an article "How I deal with SQL Server 430 million records per day", the article is a lot of recommendations

But in my eyes, the article is not much new, as I said earlier, 430 million data may be <100g data, so much data in fact with SQL Server can hold

Not much of a relationship, but with the structure of the blogger's ability to have a relationship, database architecture is nothing more than a sub-table-"sub-Library-" sub-room, to solve the cap problem, there is the problem of MVCC.

In fact, a very large database (VLDB) is not the same as a small database, I don't care about the amount of data, I just do it. The database actually takes up the volume

The first idea for managing large tables in large databases is to use table partitioning to manage it, and table partitioning starts with SQL Server2005 to SQL Server2014 or table partitioning

People will say, Microsoft still no breakthrough , or use such an old table partitioning technology to manage the big table, and even sometimes I do the table partition, but the whole database is so large, I can not do a full backup ...

Seemingly in the eyes of everyone, table partitioning is just a tool to improve query performance, and more directly, it is the weapon that improves the performance of select query.

But for us DBAs, the advantages of partitioned tables are more of a management

The administrative advantages of partitioned tables are as follows:

1. Compress data for a separate partition (SQL2008)

2. By partition statistics (SQL2014 create STATISTICS and related statistics statements now allow to create per-partition statistics by using the INCREMENTAL option)

3. Regenerate data for a partition online (SQL2014 The progress report for the online Index action event class now has two new data columns: PartitionID and PartitionNumber)

4. Re-organize data for a partition online (SQL2005)

5. Filegroup Backup and piecemeal restore (SQL2005)

6, CheckFilegroup (SQL2005)

7. Swap partitions (delete historical data or archive or ETL)

8, lock upgrade can be promoted to the partition lock, instead of directly to the table lock (SQL2008)

In fact, as long as the table partition is done well, the partition interval is reasonable, scheduled maintenance partition table, hold large data volume is not much problem.

The year before last saw an article, for the relational database, if the actual size of the database to 100TB, the relational database is powerless, it is time for Hadoop to play.

I admit that if your database really has 100TB, then SQL Server may not be able to handle, but your database is really reached 100TB, then your company's database architect

is not the responsibility to take this risk, is not his database architecture is not well, there is no sub-Library sub-table sub-room.

I don't care if the relational database is capable of handle such a large amount of data, then I saw another video.

Video Inside Oracle Technology Products division general Manager Wu Chengyang introduced the difference between the relational database and Hadoop, which is the processing of unstructured data

and the relational database is to deal with structured data, the focus of the two is not the same, Hadoop processing is a large number of unstructured data, general data volume PB level

The general manager of Wu Chengyang also said that unstructured data is often referred to as Web page data, Hadoop after processing the data into a relational database in order to query

So you can't equate Hadoop with relational databases.

II. Performance of SQL Server

In terms of performance, I just want to talk about IO

You know, regardless of whether Windows or Linux, the user program->os kernel-to-storage architecture, there is a set of IO interfaces between the user program and the OS kernel

Similarly, an IO interface exists between the OS kernel and the storage device, with parameters such as asynchronous, synchronous, write through for the storage device, and write back.

While the IO behavior of Linux operating system is different from that of Windows, the file system of the two is different, the IO device driver is not the same, the IO scheduling model is not the same

Because I do not have much in-depth research on windows and Linux IO scheduling, you can refer to the following two articles

Performance analysis of Linux five IO models

Performance analysis of Windows five IO models

In fact, not too bad database can reach thousands of TPS, tens of thousands of QPS, tens of thousands of concurrent connections

Because I did not personally test and no environment, it is no longer detailed

Read a book that said that the Linux IO scheduling model is better for machines using networked storage, a request from the upper layer, and a small number of network packets on the lower level

An IO request from the upper windows will emit more network packets than Linux for networked storage, as if it were the iSCSI protocol, so Oracle and MySQL running on Linux would be better

But it can be said that SQL Server has made special optimizations for Windows systems, in the TPS and QPS testing will not be far from the mainstream database, otherwise SQL Server has long since disappeared from the Earth

III. functions of SQL Server

I think SQL Server function is more complete, at least for a business database, other competitors have the functionality, SQL Server basically has

For example, restricting resources to use this feature

The SQL Server resource governor can limit the CPU, memory, IO resources that it uses for the logged-on user

and MySQL's query throttling is for

Limit the number of user changes to database data per hour

Control how many users open new connections per hour

Limit how many users are connected to the MySQL server

MySQL's query throttling is biased in terms of

I do not comment on the good or bad, I just want to say that each has its own advantages and disadvantages

So far, I have not found any other database features, SQL Server does not, or I caishuxueqian, may have Oracle features and SQL Server does not have

For example: Bitmap indexing in Oracle, and bitmap Filtering (BITMAP) operators in SQL Server

Related article: SQL server optimizer Features-bitmap filtering (BITMAP)

A lot of times just the way people do it is not the same yier, not that SQL Server does not have

Summarize

I am not a DBA for a long time, beginning contact at school, and after graduation, the company uses SQL Server, and now it brings me into the DBA industry.

For SQL Server This product, I am more grateful, and every day with you SQL Server enthusiasts to discuss how to use it, the exhilaration

Unfortunately, SQL Server in China's domestic market is less active, do not know whether there will be an end.

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

A small SQL Server DBA wants to talk about the capabilities of SQL Server