对《SQL Server中tempdb的management》的一些更正和补充 前几天看了这篇文章:SQL Server中tempdb的management 发现里面有些内容不是很准确 文章中说到: TempDB和其他用户数据库一样以Model数据库为模板创建,并且可以创建和查询表格。 与其他用户数据库不
对《SQL Server中tempdb的management》的一些更正和补充
前几天看了这篇文章:SQL Server中tempdb的management
发现里面有些内容不是很准确
文章中说到:
TempDB和其他用户数据库一样以Model数据库为模板创建,并且可以创建和查询表格。
与其他用户数据库不同的是,TempDB在每次SQL Server启动的时候都会被重新创建。
我打开SQLSERVER2005,对比了一下model数据库和tempdb数据库,发现还是有一些不同
初始大小和自动增长、跨数据库所有权链接已启用这里不讨论
跨数据库所有权链接:
页面验证里面,,model数据库页面验证用的是checksum,但是tempdb没有页面验证
tempdb不使用页面验证究竟好还是不好???
讨论这个问题之前,这里要先了解一下checksum的功能
以下摘抄自:
页面 CHECKSUM:
在数据库页面从被写入磁盘到被SQL Server读取的这段时间内,有可能由于外界原因发生损坏。
比如I/O设备的损坏,驱动的损坏或者由于电源不稳没有写完整。Checksum机制使SQL Server可以检测到这些损坏。
需要注意的是,Checksum机制只能使我们确定是不是I/O子系统引起页面损坏,但是不能自动修复它们。
作为管理员,我们可以利用这些信息来辨识出并更换损坏的I/O设备,
也可以利用重建索引或者修复损坏文件等方法修复已发生的页面损坏。
计算checksum的算法是复杂的,因为会涉及到多个页面的读写,增加CPU的负荷,可能会影响系统的吞吐量
系统吞吐量、CPU负荷这个本人觉得可以忽略不计,因为用户数据库默认都是开启了checksum页面验证的
所以本人觉得还是有必要开启checksum页面验证
页面checksum的条件:在数据库页面从被写入磁盘到被SQL Server读取的这段时间内,数据页一定要写入到磁盘,
如果数据页面没有写入到磁盘,那么开启checksum也就没有什么用处
在tempdb里,无论是数据页还是索引页还是版本存储区里的页面都有可能写入磁盘
版本存储区的资料:
版本存储区也和数据页面索引页面一样由8k大小的页组成。这些页存在缓冲池中,可以在TempDB面临内存压力时被写入磁盘
如果是这样,那么SQLSERVER应该要开启tempdb数据库的checksum页面验证才对啊
很可惜,本人暂时还没有在网上找到相关解释
不过通过下面实验,本人猜测了一些SQLSERVER团队的意图
这些实验在文章SQL Server中tempdb的management里已经给出了,本人只是做一些补充
实验
以下实验的实验环境:Windows7,SQLSERVER2005个人开发者版
全局临时表
通过创建##t_tempdblog全局临时表,插入数据,修改数据,删除数据,建立聚集索引,删除聚集索引,建立非聚集索引,删除非聚集索引
查看事务日志文件里有没有相关的操作记录
测试脚本
插入记录
1 use tempdb ##t_tempdblog ##t_tempdblog (c1 int, c2 char(1000)) () ##t_tempdblog ), 1000)) operation,context, , , fn_dblog(null, null)

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Dreamweaver CS6
Visual web development tools

WebStorm Mac version
Useful JavaScript development tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SublimeText3 Mac version
God-level code editing software (SublimeText3)