what is sql page split | sql server page split | Sql server performance tuning Part 9

Подписаться 26 тыс.

Просмотров 10 тыс.

50% 1

SQL SERVER PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS
Part-6
• nonclustered index in ...
---Script of this video
IF EXISTS (SELECT *FROM SYS.TABLES WHERE NAME ='CRICKETPLAYERS')
BEGIN
DROP TABLE CRICKETPLAYERS
END
CREATE TABLE CRICKETPLAYERS
(
NAMES CHAR(500),
TEAM CHAR(500)
)
INSERT INTO CRICKETPLAYERS VALUES ('VIRAT KOHLI','INDIA')
SELECT * FROM CRICKETPLAYERS
DBCC IND('PERFORMANCE_TUNING','CRICKETPLAYERS',-1)
INSERT INTO CRICKETPLAYERS VALUES ('MS DHONI','INDIA')
INSERT INTO CRICKETPLAYERS VALUES ('YUVRAJ SINGH','INDIA')
INSERT INTO CRICKETPLAYERS VALUES ('GLENN MAXWELL','AUSTRALIA')
INSERT INTO CRICKETPLAYERS VALUES ('JAMES MICHAEL ANDERSON','ENGLAND')
INSERT INTO CRICKETPLAYERS VALUES ('ALASTAIR NATHAN COOK','ENGLAND')
INSERT INTO CRICKETPLAYERS VALUES ('ANDRE RUSSELL','WEST INDIES')
INSERT INTO CRICKETPLAYERS VALUES ('CHRIS GAYLE','WEST INDIES')
INSERT INTO CRICKETPLAYERS VALUES ('FAF DU PLESSIS','SOUTH AFRICAN')
DBCC IND('PERFORMANCE_TUNING','CRICKETPLAYERS',-1)
DBCC TRACEON(3604)
DBCC PAGE ('PERFORMANCE_TUNING',1,2489,3)
Find Us On RU-vid- "Subscribe Channel to watch Database related videos" / @ssunitech6890
For Quiz-
• sql server : Interview...
Find Us On FaceBook-
/ ss-unitech-18770538867...

Опубликовано:

2 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 31

@pavankumar-ds7xr 4 месяца назад

Page split will degrades the performance. How to troubleshoot it

@ssunitech6890 4 месяца назад

I explained the command in the video

@kirankumar-vz6jj 3 года назад

What if index is not their, insert happens to last page? Instead of page splitting

@ssunitech6890 2 года назад

It will automatically added in last page

@UmpikLumpik Год назад

1:41 When the split occurs on Page 1, which is of size 5. Then Page 1:1 will be of size 5 and Page 1:2 also of size 5? Or the logic is that new split will be the smallest possible? So Page 1:1 is 3+1(new record) and Page 1:2 will be the rest of that(2)? What I don't understand is Row - Offset and also what values can I use in DBCC PAGE as parameter FILE NUMBER? What represents that File Number and why do we have 1 there?

@MiladNabi-k7f 4 месяца назад

your lab does not match you slide. this is normal since one page can hold 8KB data, where in your lab a page split happened like you presented in slide page 1:1 and 1:2

@meetvishal187 3 месяца назад

good information

@ssunitech6890 3 месяца назад

Thanks

@jaideepmalik1028 5 лет назад

Gajab

@ssunitech6890 5 лет назад

Dhanywaad Jaideep

@pinkbuddy1061 4 года назад

Hi Sushil, it a very nice video, but i am not clear how Page split causes performance issue? if it does performance issue then how can we avoid page split? it would be great if you could let us know, Thanks in advance

@ssunitech6890 4 года назад

Hi Pink, Thanks for your appreciation. Page split causes problems because if you would have only 5 pages at initial so it would need to scan only 5 pages to get the data. If new record will be inserted into table then some data from existing page will go into new page and now existing page will have blank space that will not fill again and now it need to scan 6 pages instead of 5. If alot of page splits happen then alot of blank spaces in the pages. That's why it causes the performance issues. Thanks.

@lakshmiprasannapentyala1581 5 лет назад

Nice Can you provide me with the details, as I wanted to put 3 yrs of experience as an Sql server developer . I will pay you if you support me

@ssunitech6890 5 лет назад

Thanks Lakshmi Prasanna for your comment.. Please share to others.. Thanks-SS Unitech

@ankushmankar7455 5 лет назад

Excellent video, very useful information. I request if you could provide the scripts used in the example in video description. Thank you very much. :)

@ssunitech6890 5 лет назад

Thank you Ankush for your comment and suggestion. I have added the script in the description of the video. If you have more suggestions please let me know we will try to improve.. Thanks-SS Unitech

@sree0081 3 года назад

Record size is showing 1007 can u explain bout this. u given only 500+500 right?

@shubhangiagarwal3327 5 лет назад

Nice video...

@ssunitech6890 5 лет назад

Thanks

@ShivKumar-ed6el 5 лет назад

Nice bro... Carry on

@ssunitech6890 5 лет назад

Thanks Shiv

@prasanthbobby270 4 года назад

Thanks for the information, Sushil. I've a query. Too many page splits will cause performance issue. But i didn't understand how to avoid too many page splits. From the video I can understand how page split occurs. Am i missing something? Please clarify.

@ssunitech6890 4 года назад

Hi Prasanth, Thanks for your appreciation.. I have explained in this video. For example- if we have a table that would have only one column and size is varchar(5000). So we try to reduce it by varchar (4000) because if we use 5000 then only one row can be occupied for each page...if we use 4000 then 2 rows can be occupied in each Page.. So less page split cause good performance.. Still have any doubt please let me know will try to explain another way. Thanks-SS Unitech

@prasanthbobby270 4 года назад

@@ssunitech6890 Understood the concept. But in real scenarios there will be thousands of pages. Then how to identify the performance is decreasing by page splits?

@UmpikLumpik Год назад

@@prasanthbobby270 I think by calculating (page size - columns size count) In example like you have 8000 bytes/page and you'll have 4 VARCHAR fields of 2KB each, then it will be okay.... But let's imagine one of the VARCHARS would have size of 2,5KB, then depends on how much data in this VARCHAR would be inserted you would get pages split. The solution would be to or having 2KB strict, or when more is needed, then redesign tables so you'll move this field to another table with some kind of logic in it. That's how I understand it, but I can be wrong.

@roshnisingh7661 4 года назад

Excellent video on performance tuning

@ssunitech6890 4 года назад

Thanks

@Some_Little_Knowledge 4 года назад

nice videos

@ssunitech6890 4 года назад

Thanks Faizan. Please share to others. Keep watching videos.

@Some_Little_Knowledge 4 года назад

why you do not include Pakistan team

@ssunitech6890 4 года назад

Hi Faizan, Sorry I forgot to add Pakistan team in this video.. but I added in other videos like-ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-EgPd0i2PcHE.html Thanks-SS Unitech