谁懂 database tuning

乱码

原帖由 marklan001 于 2011-1-24 20:43 发表

个人感觉Temp Table，Table Variable and CTE适用的场合不同
1. DATE SET很大且需要INDEX的时候用应当用Temp Table，不需要INDEX的时候用Table Variable。
2. 当SERVER内存充足时，Temp Table，Table Variable会保存在内存 ...

仔细查了一下资料，得到下面的东西：

关于temp table(inside ms sql server tsql programming,chaper2)：
Temporary tables are created in tempdb, regardless of the database context of your session. They have physical representation in tempdb, although when they're small enough and Microsoft SQL Server has enough memory to spare, their pages will reside in cache. SQL Server will persist the temporary table's pages on disk when there is too little free memory.

关于table variable(inside ms sql server tsql programming,chaper2):

To dispel what probably is the most widespread myth involving table variables, let me state that they do have physical representation in tempdb, very similar to temporary tables. As proof, run the following code that shows which temporary tables currently exist in tempdb by querying metadata info, creates a table variable, and queries metadata info again:

SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%#%';
GO
DECLARE @T TABLE(col1 INT);
INSERT INTO @T VALUES(1);
SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%#%';

When I ran this code, the first batch returned no output, while the second returned #0CBAE877, which is the name of the temporary table in tempdb that represents the table variable @T. Of course, you will probably get a different name when you run this code. But the point is to show that there is a hidden temporary table created behind the scenes. Just like temporary tables, a table variable's pages will reside in cache when the table is small enough and when SQL Server has enough memory to spare. So the discussion about aspects of working with temporary tables with regard to tempdb applies to table variables as well.

---------
还有sql server central 的一篇文章：
http://www.sqlservercentral.com/Forums/Topic415829-338-1.aspx



cte performance最大的好处是可以让sql inline,让sql server engine自己作优化，这也是为什么几个cte之间无法用profiler来做tracing,逻辑上清晰它跟temp table/table varialbe是一样的，没什么优势。

[ 本帖最后由 乱码 于 2011-1-25 11:28 编辑 ]

典

原帖由 乱码 于 2011-1-24 21:43 发表


仔细查了一下资料，得到下面的东西：

关于temp table(inside ms sql server tsql programming,chaper2)：
Temporary tables are created in tempdb, regardless of the database context of your session. They have ph ...

The Example is misleading
If you add a GO (see the red color), you will see the difference.
When we create a temp table, we can get the boject_id, and then get the data_page,
With table variable, no object_id, no way to see the data_page. There may be a special data_page for table _variable but it won't be part of the database.That's why no statistics for table_variable, index cannot be maintained,

SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%#%';
GO
DECLARE @T TABLE(col1 INT);
INSERT INTO @T VALUES(1);
GO
SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%#%';

[ 本帖最后由 典 于 2011-1-25 08:49 编辑 ]

典

For temple table we can track in to data pages (see the example below), the last query shows 3 pages and the first page is 0xEA0100000100 (in my system).
For table variable, it's impossible to track into pages,
----------------------------------------------------------------

SELECT top 10 * INTO #tempTable
FROM [master]..spt_values
go

SELECT * FROM #tempTable
go

select OBJECT_NAME(object_id) as name, rows,type_desc,total_pages,first_page
from sys.partitions p join sys.system_internals_allocation_units a on p.partition_id=a.container_id
where object_id in (object_id(N'tempdb.dbo.#tempTable'))

典

原帖由 乱码 于 2011-1-24 21:43 发表


仔细查了一下资料，得到下面的东西：

关于temp table(inside ms sql server tsql programming,chaper2)：
Temporary tables are created in tempdb, regardless of the database context of your session. They have ph ...

One of the post stated "Table variables can have a primary key, but indexes cannot be created on them,"

That's not correct.
Actually Indexes created implicitly by the creation of primary keys or unique constraints

乱码

原帖由 典 于 2011-1-25 09:46 发表


The Example is misleading
If you add a GO (see the red color), you will see the difference.
When we create a temp table, we can get the boject_id, and then get the data_page,
With table variable, n ...

Go是一个client side batch command,你不能在一个batch create table variable，在另外一个batch access它.

DECLARE @T TABLE(col1 INT);
INSERT INTO @T VALUES(1);
select * from @T

可以到的结果，因为他们是一个batch.

DECLARE @T TABLE(col1 INT);
INSERT INTO @T VALUES(1);
go
select * from @T

这样就不会得到结果，因为select * from @T已经出了batch的context了。

下面的article比较有用：

http://itknowledgeexchange.techt ... variables-and-ctes/

其中提到两点：

Table variables are destroyed as the batch is completed.
Temp tables can be used throughout multiple batches.

[ 本帖最后由 乱码 于 2011-1-25 11:01 编辑 ]

乱码

原帖由 典 于 2011-1-25 10:16 发表


One of the post stated "Table variables can have a primary key, but indexes cannot be created on them,"

That's not correct.
Actually Indexes created implicitly by the creation of primary keys or u ...

agreed!!!

典

原帖由 乱码 于 2011-1-25 09:52 发表


Go是一个client side batch command,你不能在一个batch create table variable，在另外一个batch access它.

DECLARE @T TABLE(col1 INT);
INSERT INTO @T VALUES(1);
select * from @T

可以到的结果，因为他们是一个 ...

Ageed...
What I want to say is, I feel table variable won't have Normal data_page, won't have logs / disk storage (and IO) as templ table has.
Only if there is not enough memory avalible then the system writes  table variable data back to disk.

[ 本帖最后由 典 于 2011-1-25 10:12 编辑 ]

乱码

原帖由 典 于 2011-1-25 11:09 发表


Ageed...
What I want to say is, I feel table variable won't have Normal data_page, won't have logs / disk storage (and IO) as templ table has.
Only if there is not enough memory avalible then the sy ...

在我们的post uat上作了一下实验，小的table variable的确没有io

混不到坑的萝卜

Junior的developer不需要知道，senior的不知道不关心就说不过去了。

47楼推荐的这本是本好书，适合developer学习：
Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization

对大数据量的表进行partition，也许性能提高上是出奇效的方法之一。不过这已经有点超出developer应该掌握的范围。

乱码

原帖由 混不到坑的萝卜 于 2011-1-25 13:23 发表
Junior的developer不需要知道，senior的不知道不关心就说不过去了。

47楼推荐的这本是本好书，适合developer学习：
Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization

对大数据量的表进行partition， ...

covered index跟table partition提高performance原理一样，但需要的改变不是一个级别的,前者要小很多

混不到坑的萝卜

原帖由 乱码 于 2011-1-25 13:47 发表
covered index跟table partition提高performance原理一样，但需要的改变不是一个级别的,前者要小很多

前者的改变小得多，但性能提高有限。

table partition则完全是不一样的概念。你甚至可以把一个大表割裂成多个表，每个放在不同的硬盘甚至SSD盘上，对性能的提高是让你再仔细优化的query连拍马都赶不上的。当然，这牵扯到了比sql performance tuning更高级的performance planning。。。

澳贼

原帖由 混不到坑的萝卜 于 2011-1-25 16:26 发表

前者的改变小得多，但性能提高有限。

table partition则完全是不一样的概念。你甚至可以把一个大表割裂成多个表，每个放在不同的硬盘甚至SSD盘上，对性能的提高是让你再仔细优化的query连拍马都赶不上的。当然，这牵扯到了比sql performance tuning更高级的performance planning。。。

小强不愧是大公司出来的。

flyspirit

原帖由 混不到坑的萝卜 于 2011-1-25 16:26 发表

前者的改变小得多，但性能提高有限。

table partition则完全是不一样的概念。你甚至可以把一个大表割裂成多个表，每个放在不同的硬盘甚至SSD盘上，对性能的提高是让你再仔细优化的query连拍马都赶不上的。当然，这牵扯到了比sql performance tuning更高级的performance planning。。。

performance planning是提高性能的终极解决方案，不过代价不菲。

乱码

原帖由 flyspirit 于 2011-1-27 09:21 发表


performance planning是提高性能的终极解决方案，不过代价不菲。

嗯，前头台都要大变，如果没有特殊的原因，这种要求没人敢提，９０％基本上会被否了的。而且如果不是对已有系统很有经验，这种问题在data modeling阶段也不会被target...

flyspirit

原帖由 乱码 于 2011-1-27 09:28 发表


嗯，前头台都要大变，如果没有特殊的原因，这种要求没人敢提，９０％基本上会被否了的。而且如果不是对已有系统很有经验，这种问题在data modeling阶段也不会被target...

还记得我和你提的app server吗？

这种情况下它的威力就发挥出来了。 只要改 application 这一层就可以了。

变动量看怎么paritation了，虽然麻烦但也不是不能完成。 业务量达到一定程度只能走这条路。

[ 本帖最后由 flyspirit 于 2011-1-27 09:42 编辑 ]

乱码

原帖由 flyspirit 于 2011-1-27 09:41 发表


还记得我和你提的app server吗？

这种情况下它的威力就发挥出来了。 只要改 application 这一层就可以了。

变动量看怎么paritation了，虽然麻烦但也不是不能完成。 业务量达到一定程度只能走这条路。

right！！

app layer是一定要改的，web layer到不一定。